How to create a Search box outside of the main menu (1 Viewer)

sbrown106

Member
Local time
Today, 16:27
Joined
Feb 6, 2021
Messages
77
Hi everybody,

Hope somebody could help me with this please
I am trying to redesign my main menu and move a vba search box so it sits
outside and above the main navigation menu on the form . The field I am trying to search for, lies
in more than one form which the user can select by selecting a differenty tab on the navigation menu,
eg Customer Name, may be in a Clients Form, Manufaturers Form etc. So I would like
one search box that sits outside and above the naviagtion menu, without having the same
search box within each page. I hope that makes sense

I have tried a simple example below with just one of the forms (CustomerForm) but cant get it to work

Is it possible to do this? any help would be great thanks

it works if the search box is in the 'details' bit of the form with fldNameID eg,

strCriteria = "[fldNameID] = " & Me![Text27]

menun form = frmNavForm
table = tblCustomers
form = frmCustomersForm (selected from tab on menu)
text box = Text27
button = btnFindCustomer


Private Sub btnFindCustomer_Click()

Dim strCriteria As String

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

strCriteria = "tblCustomers![fldNameID] = " & frmNavForm!frmCustomerForm![Text27]

rst.FindFirst strCriteria

Me.Bookmark = rst.Bookmark

End Sub

many thanks for any help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
This would likely involve checking which one is the current tab. Can you post a sample db?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2002
Messages
42,970
If you are using an Access navigation form, only a SINGLE subform can ever be open at one time.

If you want to use this method, bind the subforms, to a query with a where clause that references the control:

Where CustomerID = Forms!mainform!cboCustomerID

In the AfterUpdate event of the combo, requery the navigation form. Whichever subform is loaded will be refreshed to the value in the combo.
 

sbrown106

Member
Local time
Today, 16:27
Joined
Feb 6, 2021
Messages
77
This would likely involve checking which one is the current tab. Can you post a sample db?
Hi theDBguy, I have made something up that tries to explain a bit better. Excuse my lack of imagination! but hopefully the principle is there
Basically trying to see if I can get a search function to work outside of the menu to pick up the patientid (becasue it will exist in more than one form) So if a user picks a patientID, it will find that patientID in whichever forms that exist in the menu, instead of having the same search function on each form. I'm new Access and my knowledge isnt great- so it may not be possible, thanks for your reply
 

Attachments

  • database_test.accdb
    568 KB · Views: 520

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks for posting a sample db. When MS introduced the Navigation Forms, they also introduced the new BrowseTo command.

I have decided to go with that approach. Take a look at the attached modified copy of your sample db and let us now if this approach will work for you.

Also, when you go to the Hospital tab, see what happens and let us know if you can tell us why that is. If not, I can explain.
 

Attachments

  • database_test.zip
    42.7 KB · Views: 334

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2002
Messages
42,970
I didn't suggest that method in case it raises an error if the form you are on does not include PatientID. That is why I recommended the "pull" method since only the subforms that support a PatientID filter would have the criteria in its RecordSource
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:27
Joined
May 7, 2009
Messages
19,169
test.
 

Attachments

  • database_test.accdb
    696 KB · Views: 232

sbrown106

Member
Local time
Today, 16:27
Joined
Feb 6, 2021
Messages
77
Hi. Thanks for posting a sample db. When MS introduced the Navigation Forms, they also introduced the new BrowseTo command.

I have decided to go with that approach. Take a look at the attached modified copy of your sample db and let us now if this approach will work for you.

Also, when you go to the Hospital tab, see what happens and let us know if you can tell us why that is. If not, I can explain.
Thanks DBGuy for that and the rest of you guys, I'm guessing that the Hospital tab is blank because I forgot to change the datatype to number (there may be another reason)

if I could have some further advice please - then will leave this search box alone!

Within the Hospital form in attachment I have written something that works with a msgbox (but just with the hospital form), so I am now just trying to adapt this a bit to what you have sent me, just to have a msgbox confirm if I have a value or try again.
I can understand how the IsNull(Me![Text31]) Or (Me![Text31]) = "" function works here in my hospital form, but in your code you use Me. and not Me!, so does IsNull(Me![Text31]) Or (Me![Text31]) = "" just replaced by IsNull(Nz(Me.Text14, 0))="" but not really sure what else I need to change.
Although I have a feeling its not going to be something simple!

Thanks again
Hi theDBguy, I have made something up that tries to explain a bit better. Excuse my lack of imagination! but hopefully the principle is there
Basically trying to see if I can get a search function to work outside of the menu to pick up the patientid (becasue it will exist in more than one form) So if a user picks a patientID, it will find that patientID in whichever forms that exist in the menu, instead of having the same search function on each form. I'm new Access and my knowledge isnt great- so it may not be possible, thanks for your reply
 

Attachments

  • database_test2.accdb
    872 KB · Views: 527

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2002
Messages
42,970
Before you get much further, you will have a much easier time next month or next year if you rename controls so that they have meaningful names. Text31 doesn't help anyone who has to read your code.

PS, Changing the name of a control AFTER you have added event code, will orphan the event code so you will have to open the VBA window and change the event procedure names at the same time and of course compile so you can change any other references to the control name.

All-in-all, it is best if you train yourself to do this IMMEDIATELY so you don't even have to think about it later.

Did you check? Do you get an error if the visible form does not have a bound control that matches the code running on the main form?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
21,358
I'm guessing that the Hospital tab is blank because I forgot to change the datatype to number
You got it! Try changing it to Number and see what happens.
 

sbrown106

Member
Local time
Today, 16:27
Joined
Feb 6, 2021
Messages
77
Before you get much further, you will have a much easier time next month or next year if you rename controls so that they have meaningful names. Text31 doesn't help anyone who has to read your code.

PS, Changing the name of a control AFTER you have added event code, will orphan the event code so you will have to open the VBA window and change the event procedure names at the same time and of course compile so you can change any other references to the control name.

All-in-all, it is best if you train yourself to do this IMMEDIATELY so you don't even have to think about it later.

Did you check? Do you get an error if the visible form does not have a bound control that matches the code running on the main form?
Hi Pat, thanks for your comment, I have changed my code as you say. I had been using Hungarian notation in the database I am working on but got lazy in this example, sorry. I didnt get an error with the form not having a bound control, but did have to update my code as the textbox, button numbers changed
 

Users who are viewing this thread

Top Bottom