Run a search on load for one-to-many related tables

[keviny04]

QuantifyRisk,

I remember you said you had two SEPARATE forms, one for searching and one for data entry. In that case, you do need a bit of code to pass info from the search result to the data entry form. I attached a modified version of Browne's form here: search2000.zip

You open the client search form, search for a client, select a client, then click the "Edit Client" button that I made. It will open a data entry form with the client's info. Is this what you want? In that case, the button's OnClick event is simple:

DoCmd.OpenForm "frmClient", , , "[ClientID]=" & Nz(Me!ClientID, -999)

The Nz() function is to trap null value of ClientID. (This is due to Browne's decision to always return at least one search result, a row of null values, even when nothing is found. To me, this is a questionable move that would confuse the user. If nothing is found, the search result should show an empty list. But it would take additional code to do that.)

For this to work, frmClient must be bound to the Client table. If you want to see data from other tables included on the form, then frmClient must be bound to a query that joins all those tables together, as I suggested.

You said you didn't want to use the search form while entering data. Then just make the data entry form MODAL. The user won't be able to leave the data entry form until it is closed.

[QuantifyRisk]

I cannot open the attachment at work. But how can I do this:

"If you want to see data from other tables included on the form, then frmClient must be bound to a query that joins all those tables together, as I suggested."

Are we talking joins here? Never done one before.

[keviny04]

I cannot open the attachment at work. But how can I do this:

"If you want to see data from other tables included on the form, then frmClient must be bound to a query that joins all those tables together, as I suggested."

Are we talking joins here? Never done one before.

Yes, you need SQL that joins tables together. For instance, I added a "Level Description" column to Browne's form:



Since the Level Description field (actual field name is "Level") is in another table, you need this SQL for the form's control source that links to that table:

SELECT tblClient.*, tblLevel.Level FROM tblLevel INNER JOIN tblClient ON tblLevel.LevelID = tblClient.LevelID;

...instead of just tblClient that was before.

[QuantifyRisk]

That's awesome. Thank you.