Search Form VBA Code

[PatAccess]

Hello,

I have several forms that I would like to search and open if a condition is met. How do I write the code on the command button. Write now I have

DoCmd.OpenForm "", acNormal, , "EqpmntTypeID=" & Me.cboEquipment & ""

Obviously it's not correct and I need help!

Thank you!

 

[Ranman256]

this text is no a search...unless you mean ,you search on a form of records,
click button to open another form

you did not include your form name:
DoCmd.OpenForm "MYFORM", , , "EqpmntTypeID=" & Me.cboEquipment & ""

this will work assuming EqpmntTypeID is numeric.
and the code goes into the BUTTON CLICK event.

 

[PatAccess]

I have a form with a combo box and a search button. When I choose an equipment I need it to search my entire database where that equipment applies and open all the forms where it finds it. I don't want to open just a specific form, just anyone where it matches that equipment.

Thank you,

 

[Mark_]

Do you mean you want to return a record set with all matching values OR do you want to return a record set with all matching values then open an update form for each match?

 

[jdraw]

When I choose an equipment I need it to search my entire database where that equipment applies and open all the forms where it finds it.

The Equipment will be found in your table. A Form is like a window looking into a Table.
Data resides in a table, not on a Form.

If you also want to know which of your forms exposes or allows you to see that data, that would be a different question. But I don't think that is what you are asking.

Perhaps you could give us an example of what you are searching for (equipment) and what you expect as a result.

Good luck.

 

[PatAccess]

Do you mean you want to return a record set with all matching values OR do you want to return a record set with all matching values then open an update form for each match?

Hello,

I want to return a record set with all matching values then open an update form for each match.
So when I choose the equipment, I want to All forms to open with the matching value
So I have several forms and I want access to go look in ALL my tables and open the forms that matches the equipment I'm looking for

Thank you,

 

[Mark_]

So I have several forms and I want access to go look in ALL my tables and open the forms that matches the equipment I'm looking for

Do you mean "Records" which are the entries in a table that hold data?

As jdraw pointed out, you have records in a table, not FORMs. A form displays an individual record.

Until we are understanding what you are really trying to do, giving advice is difficult.

 

[PatAccess]

Do you mean "Records" which are the entries in a table that hold data?

As jdraw pointed out, you have records in a table, not FORMs. A form displays an individual record.

Until we are understanding what you are really trying to do, giving advice is difficult.

So I have a form with fields from a table yes. I now have a form with a search command button and a combo box where I can choose an equipment type and subsequently I would like to for the cmd button to open ANY form that have that matches that equipment type and it's records. Here an example I have on another database

Private Sub cmdSearchName_Click()
DoCmd.OpenForm "Frm_Travel", acNormal, , "[EmpID]='" & Me.cboFName.Column(0) & "'"
End Sub

In this one, "Frm_Travel" is designated so it will open that form but what if I want it to open any form with fields records matching the [EmpID] I select?
Maybe it is a recordset I need but I'm not sure

Thank you for the help

 

[jdraw]

Please tell us what you mean by this

[COLOR="Blue"] open any form with fields records matching the [EmpID] [/COLOR]

If you are looking for records with a specific EmpID, you could use a form to display the info. Why all forms???
If you are looking for all forms that have EmpID in their recordsource, then let's be clear on what exactly you need/want.

I think the confusion is related to the terminology being used. I still don't understand why the forms need to be opened??

I have a routine that will dynamically review all your tables and identify all fields and store the info in another table (like a mini dictionary). I'm not sure if you are trying to modify the field EmpID or update the value of a specific EmpID.

 

[PatAccess]

Please tell us what you mean by this

[COLOR="Blue"] open any form with fields records matching the [EmpID] [/COLOR]

If you are looking for records with a specific EmpID, you could use a form to display the info. Why all forms???
If you are looking for all forms that have EmpID in their recordsource, then let's be clear on what exactly you need/want.

I think the confusion is related to the terminology being used. I still don't understand why the forms need to be opened??

I have a routine that will dynamically review all your tables and identify all fields and store the info in another table (like a mini dictionary). I'm not sure if you are trying to modify the field EmpID or update the value of a specific EmpID.

Ok so I see what you mean and I've set it that way. Now I get the following error message "Runtime Error 3000 and Reserved Error 3201." Here is what I am trying to do, what am I doing wrong?

Private Sub cmdSearch_Click()

'DoCmd.OpenForm "Frm_AHU", acNormal, , "EqpmentTypeID='" & Me.cboEquipment.Column(1) & "' AND [SurveyDate]=#" & Me.txtDate.Value & "#"
'DoCmd.OpenForm "Frm_EF", acNormal, , "EqpmentTypeID='" & Me.cboEquipment.Column(1) & "' AND [SurveyDate]=#" & Me.txtDate.Value & "#"
'DoCmd.OpenForm "Frm_P", acNormal, , "EqpmentTypeID=" & Me.cboEquipment & ""
End Sub

 

[jdraw]

Is this the correct spelling
EqpmentTypeID

I still don't understand why you are opening forms???

Most often you have a bunch of records, and you want to find a record or records based on same field values.
You could have 1 form searching for records from a table or query.

Can you give us an example of what you are searching for and what you want to be returned?

See this Allen Browne article for more info.

 

[Mark_]

Ok so I see what you mean and I've set it that way. Now I get the following error message "Runtime Error 3000 and Reserved Error 3201." Here is what I am trying to do, what am I doing wrong?

Private Sub cmdSearch_Click()

'DoCmd.OpenForm "Frm_AHU", acNormal, , "EqpmentTypeID='" & Me.cboEquipment.Column(1) & "' AND [SurveyDate]=#" & Me.txtDate.Value & "#"
'DoCmd.OpenForm "Frm_EF", acNormal, , "EqpmentTypeID='" & Me.cboEquipment.Column(1) & "' AND [SurveyDate]=#" & Me.txtDate.Value & "#"
'DoCmd.OpenForm "Frm_P", acNormal, , "EqpmentTypeID=" & Me.cboEquipment & ""
End Sub

So you are trying to edit THE SAME RECORD on multiple forms, correct? Or are you trying to display the same recordset on multiple forms?

For the most part, you will find it much easier if you use a single form to update a given record. Look into tab controls to show the subparts of your record in logical groupings. It will avoid a lot of issues if users start making changes on one instance then wonder why it isn't showing on another instance. It will also avoid issues with multiple forms trying to change the same data at the same time.