Dynamic search form

[tezread]

I have a dynamic search form that looks up a table with a MeetingDate column.
Values for that column are created using a list box with dates to choose from
My problem isthat the search form is showing the ID number of the Meeting date and not the date itself

see attached for demo

 

[ConneXionLost]

The source query for that combo box returns three columns of info, but you've set the combo box to only show the second. Either change the source query or change the display of the combo box.

Cheers,

 

[jzwp22]

You will have to adjust the query on which the list box is bound to include the Meeting table since the meeting date value is held within that table. Your orders table has a field called meetingdate but it is not a date field but rather a long number field that looks to be the foreign key to the MeetingID primary key of the Meeting table. I think you should clean up your field name so as to avoid confusion in the future. The reworked query would look like this:


SELECT Patient.CompanyName, Patient.ContactName, Meeting.MeetingDate
FROM Patient INNER JOIN (Meeting INNER JOIN Orders ON Meeting.MeetingID = Orders.MeetingDate) ON Patient.PatientID = Orders.PatientID
WHERE (((Patient.CompanyName) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((Patient.ContactName) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((Meeting.MeetingDate) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"));


BTW, I don't really understand what you are doing with this database as you have wine products and medical patient information.

 

[tezread]

BTW, I don't really understand what you are doing with this database as you have wine products and medical patient information.

thankyou

I am experimenting with sample Northwind db

 

[tezread]

When I press 'MDT patient discussion' I am getting an error message 'syntax error' (missing operator) in query expression PatientID =.

I am supposed to get the sub form appear for that patient though

see attached

 

[boblarson]

Private Sub Command60_Click()
On Error GoTo Err_Command60_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Patient_Discussion"
    Debug.Print Me![SearchResults]
    stLinkCriteria = "[PatientID]=" [COLOR="Red"][B]& Chr(34) &[/B][/COLOR] Me![SearchResults][COLOR="red"][B].Column(2) & Chr(34)[/B][/COLOR]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command60_Click:
    Exit Sub

Err_Command60_Click:
    MsgBox Err.Description
    Resume Exit_Command60_Click
    
End Sub

Private Sub SearchResults_DblClick(Cancel As Integer)
    
 Command60_Click
    
End Sub