search button on form

[cochise]

I have a form (frmPerfEntry) based on a query (qryPerfEntry). The purpose of the form is to enter data that is saved in a few tables. The first two fields in the form are - txtTechNum and txtDate. My goal is for the user to be able to search on both of these fields. However, my SQL is poor. I am lost on the actual SQL statement and how to incorporate it in a command button (cmdSearch). Any help would be greatly appreciated.

 

[JCRMet]

I'm not sure this is the best way to do it - but it works. Create the command button and then copy code such as this into the 'on click' property. Obviously change the names as needed.


Sub CmdButton_Click()
On Error GoTo Err_CmdButton_Click

[SearchFieldName].SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70


Exit_CmdButton_Click:
Exit Sub

Err_CmdButton_Click:
MsgBox Err.Description
Resume Exit_CmdButton_Click

End Sub

 

[cochise]

Any other suggestions? I inserted the code with changes but got the same message. For info, my table is set up with a multi-field index on TechNum and Date. Both are set to unique and primary and to not ignore nulls.

 

[Dustman]

How exaclty are you wanting to search? You might just need to use the filter by form button. This will alow the user to search using multiple fields. -Dustin
Rom 8:28

 

[cochise]

I am trying to search by the tables multi-field primary key (TechNum and Date). So I would like to have the user enter the TechNum and Date and then click the Search button and have Access goto the record that matches the primary key. This would be used to make changes or delete the searched record.

 

[Dustman]

Try placing the following in your command button. It might work.. I don't have the ability to check this right now so I'm just going off memory.

frmPerfEntry.recordsource = "SELECT * FROM qryPerfEntry WHERE TechNum = '" & txtTechNum & "' AND Date = '" & txtDate & "';"

If that works, I would suggest adding some error trapping to it. You might also want to use trim(txtTechNum) and trim(txtDate) to eleminate extra space.. it never hurts to be safe .

I'm still not sure I know how this looks. Is this a seperate form that loads the corresponding form or is all of this on the same form? -Dustin
Rom 8:28

 

[Isadore]

You might try this variation:

Private Sub cmbFindRecord_Click()
On Error GoTo Err_cmbFindRecord_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmbFindRecord_Click:
Exit Sub

Err_cmbFindRecord_Click:
MsgBox Err.Description
Resume Exit_cmbFindRecord_Click
End Sub