Searching with SubForm (1 Viewer)

JDion

New member
Local time
Today, 05:15
Joined
Jun 6, 2018
Messages
1
I've pretty much self taught myself Access VBA through books and trial and error, so I apologize if this is a stupid question, but here goes...

I have a Main form (frmMain) that contains a subform (subMain).

frmMain: Contains only OrganizationName
subMain: Continuous form that contains OrganizationName, NewField1, NewField2, and NewField3
tblOrgName: The table that contains the organizations and is the recordsource for frmMain
tblMain: the table that contains the details of the organizations and is the recordsource for subMain

All of that works just fine.

The problem I'm running into, however, is that on frmMain, I have a command button to allow the users to search OrganizationName with a partial field. (i.e. searching "city" will bring up every record that contains "city." I have these results populated onto a new form, frmOrgSearch. On frmOrgSearch, I have a command button with the goal being to go to the specific record indicated and populate it onto a new version of frmMain, which I've copied and created as frmMain2. This is where the problems begin. The search results come up properly on frmOrgSearch, but when I click the command button to go to that record, it will bring up the record on frmMain2, but NewField1, 2, and 3 of subMain are not populated and are blank. Additionally, if a record has multiple subMain records, instead of showing all the results in subMain, it shows the results as separate records of frmMain. For example, if I searched for "city" and clicked on the record to go to "New York City Shoe Business," it will show 2 records in frmMain instead of 1 record in frmMain with 2 subMain records.

Additional info that may be useful: the search criteria is found by doing a query on frmMain of "Like "*" & [Enter the value to search for] & "*"

I requery subMain after populating the results, but it still shows up as blank. I've tried using DLookup, but I'm sure I'm calling it wrong. I currently have the following under the command button to navigate to the record after the search:

DoCmd.OpenForm "frmMain2"​
Forms!frmMain!subMain.Requery​
TempLookup1 = DLookup([NewField1], "tblMain", "tblOrgName.OrganizationName = frmMain!subMain!OrganizationName"​
Msgbox (TempLookup1)​

(I did the message box to test to see if it would find the information properly, and it kind of did. It gives me an error of "The expression you entered as a query parameter produced this error: 'Ralph'"

Ralph is the value of NewField1 in the search example I'm trying to make work.

Can anyone help with this? I'm sure I probably explained it in a confusing manner, so I apologize.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:15
Joined
Feb 19, 2013
Messages
16,553
not sure about the rest of it but this would never work - it will fail on syntax

TempLookup1 = DLookup([NewField1], "tblMain", "tblOrgName.OrganizationName = frmMain!subMain!OrganizationName"

everything else being equal, it should be

Code:
TempLookup1 = DLookup("[NewField1]", "tblMain", "OrganizationName ='" & frmMain!subMain!OrganizationName & "'")

even after correcting for missing quotes, ampersands and brackets, you appear to be looking for a value in a different table (tblOrgName) to the one being used by dlookup (tblMain).

This calls into question the accuracy of the rest of your post.

Also you are telling us what you doing but not how you are doing it. Recommend you start again - provide examples of your code (copy paste, not an approximation) and make sure you surround the code with the code tags (the # button) to preserve indentation. clarify your form names - how does frmmain2 come into it? how is it different to frmMain?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 19, 2002
Messages
42,970
Rather than duplicating a form, take a step back and create a search form. I have a couple of complicated search forms. The user selects options, some of which use Like for partial searches and others are combos or date ranges. If only one record is selected by the search, the code opens the single record edit form where all the validation logic is. If multiple records are selected, the code opens a form in Data Sheet view that shows only a few of the columns and does not allow any editing at all. The double click event of one of the controls opens the single record edit form for that record. When the search form or list form open the single record edit form, they pass their form name in the OpenArgs. That allows the single record edit form to return to the correct calling form when the user closes it.
 

Users who are viewing this thread

Top Bottom