Search Record In A Subform (2 Viewers)

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
I want to find a record in a subform via texbox and button. The search box is working fine on main form via macro on the button but i am unable to set cotrols to find data on the subform

the macro
Code:
="Passport_Number= '" & [Forms]![Agent]![sect] & "'"

what should be the reference to search in the subform? Subform name is "Client"
thanks in advance
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:57
Joined
Apr 27, 2015
Messages
6,286
Sorry, but at the risk of sounding like an elitist snob, Macros are too restrictive and are for those who find VBA intimidating. I never took the time to learn them because I found VBA much more flexible and powerful.

Sorry...
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Sorry, but at the risk of sounding like an elitist snob, Macros are too restrictive and are for those who find VBA intimidating. I never took the time to learn them because I found VBA much more flexible and powerful.

Sorry...

is there any alternative way rather than VBA? like query etc?
 

Minty

AWF VIP
Local time
Today, 17:57
Joined
Jul 26, 2013
Messages
10,355
Have a look at this page, in fact I would bookmark it, as it is still useful after many years dealing with Access, it shows you how to refer to forms and subforms with the correct syntax, both from the form you are on and if you are not on the form concerned.

http://access.mvps.org/access/forms/frm0031.htm

Don't be afraid of the VBA monster. It's actually quite cuddly once you get used to it.
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Have a look at this page, in fact I would bookmark it, as it is still useful after many years dealing with Access, it shows you how to refer to forms and subforms with the correct syntax, both from the form you are on and if you are not on the form concerned.

http://access.mvps.org/access/forms/frm0031.htm

Don't be afraid of the VBA monster. It's actually quite cuddly once you get used to it.

thanks .. going to give it a try:)
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:57
Joined
Apr 27, 2015
Messages
6,286
Don't be afraid of the VBA monster. It's actually quite cuddly once you get used to it.

Don’t know if “cuddly” is the word I would use, but then you have been feeding that beast a little while longer than I have!

For Vaques0ul, you should avoid using a forms control as a parameter in a query, for a few reasons but one in particular is that if, for some reason, that form is not open, you query will prompt an unsuspecting user (and clueless) for the name of the control.

Of couse, there is a way to account for this... with the Cuddly One!
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Have a look at this page, in fact I would bookmark it, as it is still useful after many years dealing with Access, it shows you how to refer to forms and subforms with the correct syntax, both from the form you are on and if you are not on the form concerned.

http://access.mvps.org/access/forms/frm0031.htm

Don't be afraid of the VBA monster. It's actually quite cuddly once you get used to it.

its only referring to controls on forms. how to use these when searching through textbox and button on main form :/ the monster (vb) is biting me :p
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Don’t know if “cuddly” is the word I would use, but then you have been feeding that beast a little while longer than I have!

For Vaques0ul, you should avoid using a forms control as a parameter in a query, for a few reasons but one in particular is that if, for some reason, that form is not open, you query will prompt an unsuspecting user (and clueless) for the name of the control.

Of couse, there is a way to account for this... with the Cuddly One!

i tried a trick with combobox and changed the subform master field to combobox name. now the records are filtering.

but i need them to filter through textbox. so is there any trick like the combobox one to filter records on demand through textbox?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:57
Joined
Jul 9, 2003
Messages
16,244
thanks .. going to give it a try:)
I did a series of videos on how to build a Search Form for yourself using VBA. I realised there was a need for first Steps in VBA so I did this set of videos for somebody beginning VBA:-

http://www.niftyaccess.com/vba-beginner/

The above set of videos was an attempt to lead you nicely into this set of YouTube videos:-

http://www.niftyaccess.com/building-search-criteria/

which show you how to create your own Search Form. I haven't got it quite right yet, however I hope to fix this in the near future.

Sent from my SM-G925F using Tapatalk
 
Last edited:

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:57
Joined
Apr 27, 2015
Messages
6,286
I have to bow out(prior engagement) but you are in good hands. Thanks Minty, Gasman and of course our favorite Uncle, Gizmo for pinch hitting for me! Ciao...
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Before trying the cuddly monster i have given a try to macro and now getting type mismatch error

Code:
="[CGS] = '" & [Forms]![Agent]![Passenger_Details].[Form]![sect] & "'"


CGS is of type text
Agent is main form
Passenger_Details is subform on main form
sect is the searchbox
 

Minty

AWF VIP
Local time
Today, 17:57
Joined
Jul 26, 2013
Messages
10,355
I don't think you are far away,
Code:
[Forms]![Agent]![Passenger_Details].[Form]![sect]

If you click the subform CONTROL change its name to cntPassDetails . This is the control that the subform is contained in - I like to refer to them as subform containers hence my naming convention for them, it makes it obvious its the container not the form - now change it to this

Code:
[Forms]![Agent]![cntPassDetails].[Form]![sect]

It's important to understand the difference between the subform name and the container it sits in, this is where most confusion and errors happen.
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Code:
[Forms]![Agent]![cntPassDetails].[Form]![sect]


still the same error. here is the updated code
Code:
="Passenger_Name = '" & [Forms]![Agent]![cntPassDetails].[Form]![sect] & "'"


am i doing it wrong somewhere?
 

Minty

AWF VIP
Local time
Today, 17:57
Joined
Jul 26, 2013
Messages
10,355
It may be that the macro can't use a subform reference, I don't know I'm afraid as I don't use them.

Add a new button to you form and click the On Click build event option. We're going to write some VBA !

Lets start simple.

All I want you to add is one line of code

Code:
MsgBox "Your Subformvalue is :"  & Me.cntPassDetails.Form!sect

If this works then it's not hard to then use that value to search your form.
This is all assuming you have renamed your sun form container control to cntPassDetails

I'm signing off but if this works then we can progress it later or someone else will hopefully pick it up.
 

vagues0ul

Registered User.
Local time
Today, 10:57
Joined
Sep 13, 2018
Messages
103
Code:
MsgBox "Your Subformvalue is :"  & Me.cntPassDetails.Form!sect

MsgBox "Your Subformvalue is :" & Me.cntPassDetails.Form!sect
SECT is on main form not on subform..
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:57
Joined
Sep 21, 2011
Messages
14,045
Well you have to use a subform control? :banghead:

Try

Code:
Forms!Form1!cntSubform1.Form.ID

Where ID is my control on my form in the subform control cntSubform1 that is on main form Form1
 

Users who are viewing this thread

Top Bottom