Find record subform based on a code number typed into textbox on main form

[HGoodloe]

I’m working on a project in ms access 2007 that involves a main form and subform. The main form contains a record search textbook (txtFind) . I’m trying to search a record contained in the subform from the textbox on the main form, but keep getting the following error message: Run-time error 3024: Could not find file.
Main form is name: As_Designed_Room - Subform name: Avail_Plans_Subform
I checked in the ODBC user DSN, and the database is there. But I still get the error message. Here’s the code I’m working with and it works just fine with my other database projects.
Dim dbs As Database, rst As Recordset
Me.txtFind.SetFocus

If Me.txtFind.Text = "" Then
MsgBox "Contract Number", vbOKOnly, "CODE NUMBER ERROR"
Me.txtFind.SetFocus
Exit Sub
End If

This portion of the code is where error message appears >>> Set dbs = OpenDatabase("File_Room_Plans.mdb")
DoCmd.ShowAllRecords

Set rst = dbs.OpenRecordset("Select Contract_Num From Avail_Plans " & " Where Contract_Num Like " & "'*" & Me.txtFind & "*'" & ";")

If rst.RecordCount < 1 Then
MsgBox "Contract Num not in database", vbOKOnly
End If

I’ve also tried using DLookup, but can’t get that to work either. One more thing, the contract number is alphanumeric (example: B-99999). Not sure what wild cards to use.

Any assistance would be greatly appreciated and thanks in advance.

 

[dhookom]

Don't use the .Text property.
Be explicit with DAO.
Use TGML formatting when posting questions.

Try:

Dim dbs As DAO.Database, rst As DAO.Recordset
[COLOR=#4E9A06]' Me.txtFind.SetFocus[/color]

If Me.txtFind & "" = "" Then
    MsgBox "Contract Number", vbOKOnly, "CODE NUMBER ERROR"
    Me.txtFind.SetFocus
    Exit Sub
End If
[COLOR=#4E9A06]' you should be explicit with the full path to the file rather than just the name[/color]
Set dbs = OpenDatabase("\\Servername\foldername\File_Room_Plans.mdb")
DoCmd.ShowAllRecords

Set rst = dbs.OpenRecordset("Select Contract_Num From Avail_Plans Where Contract_Num Like '*" & Me.txtFind & "*';")
If rst.RecordCount < 1 Then
     MsgBox "Contract Num not in database", vbOKOnly
End If

Duane
Hook'D on Access
MS Access MVP

 

[HGoodloe]

Thanks dhookom,

The change you made to my code did work. However, when I type a contract number into the text box on the main form, instead of the subform displaying the one record related to the one contract number, it displays all other contract numbers along with the one typed in the text box. I thought maybe the DoCmd.ShowAllRecords method could possibly be the problem. When commenting out the show all records method, no records are displayed when attempting to do a search. I also went to the filter sections in the properties of the subform and made adjustments there, but to no avail.

 

[dhookom]

I'm confused by why/how you are using showallrecords. I expect the command will only work against the main form since that is where the code is running. There is nothing in your code that references the subform.

Duane
Hook'D on Access
MS Access MVP

 

[HGoodloe]

Yes, I'm working with a subform. The code I presented in my first thread on this website worked just fine with my other projects including show all records, but for whatever reason isn't working with this current one. I guess I'm a bit confused myself with what I'm trying to accomplish here. I am very appreciative of your input and help, and don't intend to take up anymore of your time. I'll keeping searching around and see what I come up with. But, thanks again and be blessed.

 

[Darrylles]

HG,

Here are my assumptions about what you want to do:

You have a 'master' form with a 'child' sub-form.
The sub-form contains many records, but displays only one at a time (which you must step through using the sub-form navigation buttons).

You want to find and display (navigate to) the 'search criteria' record in the sub-form.
This is relatively easy, but it depends on your requirements.

Have you seen this...?

faq702-4933

If it doesn't, come back.

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[HGoodloe]

Hello Darrylles,

Yes, I have a Master form with a child sub-form. And yes, the sub-form contains many records, however when I type a contract number in the record search text-box on the master form, I only want whatever record that is associated with a particular contract number to be displayed in the child form, as opposed to other records not associated with that contract number being displayed. I realize that I wasn't clear in my first thread regarding this my description and do apologize for that. But, thanks for your help and I will checkout the link you provided. Have a blessed day.

 

[Darrylles]

HG,

The sub-form should NEVER display 'sub' records that are NOT related to the Main form record - ever!

That's the whole point of master / sub forms - the sub form should only pull records 'belonging' to the single, parent record.

Forgetting your search functionality - do sub-form records show records NOT related to the parent form record?
What is the relationship between the parent form table and the sub-form table - which fields relate them?

Can you list the tables / field names please (just the main fields such a primary keys / foreign keys in the sub)?


ATB,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[missinglinq]

when I type a contract number in the record search text-box on the master form, I only want whatever record that is associated with a particular contract number to be displayed in the child form, as opposed to other records not associated with that contract number being displayed.

You apparently don't have the Link Master Fields/Link Child Fields Properties set for the Subform. That's the only way Access knows which Records in the Subform are related to a given Main Form Record.

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[Darrylles]

Yep Missing,

I was going to move on to that, however; that CAN be confusing, because the Link Master Fields/Link Child Fields are NOT on the sub-form - but on the sub-form CONTAINER.

ATB,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

HG,

If you set focus to the parent form (a text field etc).
THEN, right-click the sub-form - choose properties; it may look confusing, it doesn't have 'normal' form properties.
That's because focus has been set to the sub-form's CONTAINER.
If you right-click the sub-form again and choose properties; NOW you will see a standard set of FORM properties - for your sub-form.
(You can get to the 'container' by hovering over the sub-form 'edge').

What is happening, is that a sub-form never sits DIRECTLY on a parent form - it always needs a CONTAINER object, which is where you define HOW the sub-form records will relate to the parent form records.

On there, you'll see Link Master Fields / Link Child Fields.
Select the Master form primary key field(s) and the related Child form foreign key fields (which relate your 2 tables).

Now, the sub-form will automatically only display records in the sub-form table that are related to the master form table.

ATB,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[HGoodloe]

Darrylles,

I totally understand that the sub-form should never display sub records that are not related to Main form records, therefore the Main form and sub-form records were and are related.

I worked on the forms some more after returning to work this morning and finally got everything worked out. Everything is working just fine now. I'm now getting the desired records in the sub-form based on a related record keyed in search text box in the main form. Thanks for all your help along with others on this thread that offered some helpful tips.

 

[Darrylles]

That's great!

However, this isn't a forum that exists simply for you - it's here to help EVERYONE that has the same problem in future.

Would you like to let us into the secret that enabled your problem to go away?


ATB,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[HGoodloe]

Darrylles, just read your last thread. Sure, I can share the code that solved my record search issue. If there's a master form containing a text box used to search a record in the subform, include the following code in the click event. That code links the Master Form to the Subform.

Me.Avail_Plans_Find.LinkMasterFields = ""
Me.Avail_Plans_Find.LinkChildFields = ""
Me.Avail_Plans_Find.LinkMasterFields = "txtFind"
Me.Avail_Plans_Find.LinkChildFields = "Contract_Num"

txtFind is the name of the textbox on the master form.
In my case, Contract_Num is what I type in the textbox and upon
clicking the command button, the desired record will appear in the subform

Hope this helps and I apologize for not sharing this when I first resolved my issue. Charge it to my head and not my heart.