Help needed displaying info on my switchboard form

[NeilT123]

I have a customer based database and have a switchboard form frmSboard. The form is unbound.

On frmSboard is a multi select combobox which is used to select the relevant customer(s) and once the selection is made I have an “Accept selection” command button which runs the following code.

Private Sub BtnAccept_Click()
Dim Q As QueryDef, DB As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant

    ' Select the Farm(s).
    Set ctl = Me![cboFmAccntNo]

    For Each Itm In ctl.ItemsSelected
       If Len(Criteria) = 0 Then
          Criteria = ctl.ItemData(Itm)
       Else
          Criteria = Criteria & "," & ctl.ItemData(Itm)
       End If
    Next Itm
    If Len(Criteria) = 0 Then
    Exit Sub
    End If
         
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("QrySLTFarm")
   Q.SQL = "Select DISTINCT * From tblFarmerDetails Where [FarmAccountNumber] In(" & Criteria & _
     ");"
     Q.Close
DoCmd.OpenQuery "QrySLTFarm" ' Run the query
Dim Count As Integer ' this code counts the number of records and saves the value as "count" which is then used to determine the height of the Account Name text box
Count = DCount("*", "QrySLTFarm")
Me.[TbAccountName].Height = 315 * Count
Forms![frmsboard].Refresh 'refresh the Main form
Me.[TbAccountName].Visible = True
BtnChangeFarm.SetFocus
Me.[cboFmAccntNo].Visible = False
Me.[BtnAccept].Visible = False
[Forms]![frmsboard].[Form]![frmSfSBOARD].Requery
End Sub

I would like to record information against some of the customers records and for that information to show on my switchboard form frmSboard AND TO BE EDITABLE.

The way I thought about doing this was to have a subform based on the customers table and then filter it using the master/child properties but as the master form is unbound that does not work.

I then thought about having a subform frmSfSboard based on the FarmAccountNumber that the query QrySLTFarm in the above code creates and use that as the master and then a further subform as the child but I can’t get the subform to update when I change the customer and the last line of the code above is my last feeble attempt.

Questions:
Is there an easier way to do this?
If so, guidance please
If this is the best way can anyone help with the code I use to update the subform after changing the customer.

This is driving me nuts so any help would be very much appreciated.

 

[NeilT123]

Sorry there was a hang over from yesterday in the above code. The

DoCmd.OpenQuery "QrySLTFarm" ' Run the query

is not needed

 

[dhookom]

The way I thought about doing this was to have a subform based on the customers table and then filter it using the master/child properties but as the master form is unbound that does not work.

Why doesn't it work? I use link master/child quite often when the main form is not bound. You only need to reference a control with a value on the main form as the Link Master.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Sorry Duane my brain is totalled screwed on this. I am not really sure what you mean.

FYI I am using Access 2003.

 

[dhookom]

You stated you wanted to use the link master/child but couldn't. I'm just asking why you can't if that's what you want to do?

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

The main form has no record source and so when I click on the 3 dots next to the Link Child or Link Master boxes in the subform I get an error message saying can't build a link between unbound forms and I don't know any other way of linking forms.

 

[dhookom]

You don't need to use no wizards to do this. Just type in the control name from the Master and the field name from the subform.

You only need to reference a control with a value on the main form as the Link Master.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

In case it helps someone I sorted this by changing the code to create a temporary table rather than a query and then linked the subform to the table.