Select name combo box in form to display data in form & records in sub

[Emmali]

I have a client data form. I want to be able to select a name from a combo box and show the record of client contact info on that form, with the visit records related to that client appearing, sorted by date (most recent first) on the subform (not in tabular style). The relation is through the ClientID, but I want the (lname,fname mi.) to show, and be updateable to their individual fields in the client info table. Then I will repeat this process with a "Test Given" combo box, to select which test was given on that visit and display the 40 or so data fields pertinent to that test. Once I have this integral form for data input and correction, I will create reports for viewing the summaries, based on calculations from the numbers entered in the form.

Is this a pipe dream? I've been debugging syntax for days with no other resources; no manuals, and no books I have found have been particularly useful. That's another request - any suggestions?

Thanks, Emm.

Center for World Indigenous Studies

http://www.cwis.org

"Always carry a tuna sandwich in case of tigers."

 

[DougP]

First
Did you get the combo box to select a record?

use this code
in the Combo Boxes After update event

Private Sub Combo0_AfterUpdate()
' create a bookmark to the specific record
Dim SyncCriteria As String
Dim f As Form, rs As dao.Recordset

'Define the from object and recordset object for the AutoCAD form
Set f = Forms(Screen.ActiveForm.FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = "[YourField]='" & Me![YourField] & "'"

' find the corresponding record in the Parts table
rs.FindFirst SyncCriteria
f.Bookmark = rs.Bookmark
End Sub
--------------

The only thing you needd to change is the "YourField"
this needs to be a unique id of the record
Also if the unique Id is a number then the line needs to be this instead

SyncCriteria = "[YourField]=" & Me![YourField]
No single quotes...



DougP, MCP

 

[Emmali]

Thanks for the code.

Actually, it's telling me that:
"The record source '~sq_cVisit Record~sq_ccbxClientID' specified on this form or report does not exist."

This is a new error (before your code), which is a bit befuddling as the cbx does not have a Record Source, but has Control and Row Sources. The error only occurs once, and then allows me to "use" my unfinished form.

Emm.

 

[Emmali]

Ahhh....
This is because I misnomered my fields clientID and practitionerID thinking I would use them to look up and display names from the info tables related by the ID# to the visit record table (each visit participated in by one client and one practitioner only).

This, of course, is not remedied by my attempt at pseudoSQL:
SELECT [PractitionerContactInfo].[PName] FROM PractitionerContactInfo WHERE [Visit Data Subform].PractitionerID=[PractitionerContactInfo].[PractitionerID];
for [Visit Data Subform].[PractitionerID], and the appropriate variation for [Visit Data Subform].[ClientID]

However, on the form Visit Data cbxPName has
Row Source: SELECT [PractitionerContactInfo].[PName] FROM PractitionerContactInfo;
and the cbxClientID on the main form (Visit Record) is supposed to sync [Client Personal Info].[ClientID] with [Visit Data Subform].[ClientID], and not show [ClientID] on the subform.

Ack. Have I botched it entirely?
emm.