Search Function

[AmyLynnHill]

Greetings,
I search this forum for a search feature for subforms. I found one and attempted to copy the code and apply to my database. I getting two error messages and stumped on how to fix.

I want to be able to search by a Participant Name, SSN or Request ID. The request ID is in the subform and has a one to many relationship with the main form-frmWithContacts. The request ID is also and auto number and not a combo box which I thought might have something to do with it.

The error messages I get are Object doesn't support this property or method and can not assign a value to this object. The search works on the search form but doesn’t populate the frmWithContacts with the record.

I have attached a copy of my database. Can anyone help me? I'm fairly new with VB and I'm not sure how to troubleshoot.

 

[tranchemontaigne]

TROUBLESHOOTING:

The best way to troubleshoot is to step through the code line-by-line by using breakpoints (set breakpoints by clicking in the left margin of the code development window. When you have it right you will see circles in the margin. When you run the code execution will stop at each breakpoint until you click the RUN botton on the STANDARD toolbar. This button usually has a triangle pointed to the right. When you get to the line that causes the error you will be able to figure out where things went wrong.

GOOD PRACTICE #1:
It's also good practice to type Option Explicit in the declarations at the top of any code module. This will force you to declare every variable you use and it will eliminate the possibility of a typo causing the code to create a new variable instead of calling a value from a pre-existing variable. This saves a lot of headaches.

GOOD PRACTICE #2:
It is also good practice to use Hungarian notation (http://en.wikipedia.org/wiki/Hungarian_notation) when naming variables. I use the following prefixes for form controls

txt (text box)
lbo (list box)
cbo (combo box)
lbl (label)
cbx (checkbox)
cmd (command button)

some others I use are
frm (form)
tbl (table)
qry (query)


Using this convention makes it easier for others to read through your code.



NOW TO YOUR QUESTIONS:
1) first error is caused by a problem assigning the SSN textbox to a recordset in the

Form_Open event of frmWithContacts

Text boxes store discrete values, not recordsets. Only listbox and combo box controls are able to store the multiple values. Though I have never tried it because I do not have access to MS Access 2002 (or newer), you could assign the ROWSOURCE property of a combobox or listbox to a recordset.

Since a text box control is appropriate for your form, it might be best to either copy a discrete SSN value from the calling form and paste it onto frmWithContacts, or loop through the recordset and when you get to the right record, pull the SSN value and paste it into the textbox control

2) I think the second error is caused because you are trying to set focus on a non-existent command button named "searchcommand". I'm guessing that you really wanted to set focus upon the "Command67" command button.

Hope this helps
________
Herbalaire vaporizer