Link data between 2 tables in forms

[Guest]

I want to do the following in a form "A" with a subform "B" and a button
"choose contractor"
1. When i click "choose contractor" a new form is popping up, form
"contractors".
The contractors names is displayed in a list with a check box behind each
contractor.
2. I then choose the contractors i want by clicking the check box.
3. When i click ok, the contrator form are closing, and every choosend
contractor should b shown in subform "B".

Heres the questions;
a) How should I in general put together the form contractor including the
chech box, maybe a query?
b)Which table should the check box be placed in(My opinion is the table that
subform "B" is based on)?
c)After choosen contractors, how do I link this data to too subform "B"
after clicking OK?
d) Do I need a relationship beetwn the tablet "Contractors" and tablet "B"?

 

[Guest]

I don't think, based on your post, you need 2 forms. A better way to do this
would be with one form. The first thing on the form would be a List Box.
This you would use to select contractors. You would not need check boxes.
Just set the Multi Select property of the list box to Extended. Then you can
select from 0 to the number of contractors in you table. Once you have
selected the contractors in the list box, that list box will have an
ItemsSelected collection that will be an array of all the contractors
selected. The you will have to construct a query based on those selections.
Here is a link that gives good information on how that is done:

http://www.mvps.org/access/forms/frm0007.htm

I use a slightly different version of that using the IN qualifer rather than
the OR:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[Guest]

I been studying your answer a little bit, and since i am a amateur in
programming etc, i allow myself to ask some suppling questions.
- After doing my choice in the list, what do i do too confirm that i want
them, and only them to be shown? Click a button or what?
- What if i want to add som choices, how will it be done?
- Where do i place the logic code, in the form, in the query, in a button or
anywhere else?

I will try to construct the form further based on the answers and try to get
in
Hope this is not to stupid questions.

Must also say this is a great forum, i had many problems solved here.

 

[Guest]

I been studying your answer a little bit, and since i am a amateur in
programming etc, i allow myself to ask some suppling questions.
- After doing my choice in the list, what do i do too confirm that i want
them, and only them to be shown? Click a button or what?

You could have a button with code behind it to display a list of the
selections and ask for confirmation.

- What if i want to add som choices, how will it be done?

I don't know what you mean. Additional choices in the list, or add items
that are not in the list. If you want to go back and add selections from the
list, you can just set the focus back to the list box and allow additional
choices.

- Where do i place the logic code, in the form, in the query, in a button or
anywhere else?

I would suggest you put the code I posted behind a command button that will
load the selected contractors into your form.

 

[Guest]

Need some more help please.
I build a table called constructor (lets call it A to make it easy), a form
with a listbox (called B) and a query qryofferer (called C).
Where do I place A, B and C in the logic you suggested?
If you place them I will do some try and failure.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


Regards

 

[Guest]

For the code below, you would pass it the name of your listbox(B):
strCriteria = BuildWhereCondition("NameOfListBoxControl")
What is returned will be based on what you selected in the listbox. If you
selected no items, an empty string ("") will be returned. If one item is
selected (let's say "Smith") then the return will be "= 'Smith'". If
multiple selections are made, then it will return: "IN ('Smith', 'Jones',
'Fromby')"

Now you have have to complete the actual criteria for your query. If you
are using a stored query(qryofferer), you will have to modify the SQL
statement to get the result you need. My recommendation would be to open
your query, switch to SQL view, copy the code, and paste it into your code.
This way, you rebuild the query each time. Here is how that works:

Dim qdf as QueryDef
Dim strSQL as String
Dim strCriteria as String

' Define the object
Set qdf = CurrentDb.QueryDefs("qryofferer")
strCriteria = BuildWhereCondition("NameOfListBoxControl")
'Here is where you would paste the SQL you copied from your query plus the
WHERE
'Change the offerer.contractor to whatever field you are filtering on
strSQL = "SELECT * FROM offerer WHERE offerer.contactor " & _
strCriteria & ";"
'Write it back to the query
qdf.SQL = strSQL

That's pretty much it.