Search Recording Using A Form 1

[ktchan741]

I am new to Microsoft Access.
I created a Table called Table_Drawing containing data column Drawing_No and Rev.

Can anyone advise how to create a Form that ask user to key-in the Drawing_No. If the Drawing_No is found in the Table_Drawing, the Rev available will be shown in the same form (in pull-down menu), allowing user to select the Rev.

If the Drawing_No is not found, the message "NO RECORD FOUND"will appear.

Appreciate advise pls.

Thanks.

 

[jimb0ne]

Hi ktchan741,
I'd do this by creating a form in design view. Set the forms recordsource property to Table_Drawing. You want to place a textbox on the form, this is where the user will enter the drawing_no value. Then place a command button, do not use the wizard that appears for the command button. Change the command buttons name to COMSearch. Change the textbox name to TXTDrawingNo. Next, place a listbox on the form as well. Set the Row Source Type property to "Value List". Enter each value you want available in the listbox in the Row Source property, seperating the values with semicolons. Rename the listbox to LSTRev. You will want to set the LSTRev's controlsource property to Rev.

Place the following code in the On_Click event of COMSearch.

dim rs as dao.recordset

set rs = me.recordsetclone

rs.findfirst "Drawing_No = " & txtdrawingno.value

if rs.nomatch = true then
msgbox "NO RECORD FOUND", vbokonly, "No Record Found"
else
me.recordset.bookmark = rs.bookmark
end if
rs.close

this code will search for the drawing number entered in the textbox when the user clicks the button. It will then figure out if there was a match, inform the user if there wasn't, otherwise, set the value of the listbox to that of the Rev field associated with the Drawing_No field which was searched for.

hope this helps, let me know.

 

[ktchan741]

Hi jimbOne,

Thank you for the advise.
However, when I click on the COMSearch, there is an error. In the microsoft VB, the error is "Complied Error: Method or Data Member Not Found" and it highlighted ".value" of the rs.findfirst"Drawing_No =" &txtdrawingno.value

Can you advise, please.

Thank you,

kt

 

[jimb0ne]

are you sure that the control you naemd txtdrawingno is a textbox and not a label? the textbox has to have a .value please check and let me know. If it still isn't making sense, email it to jtseleie@theedge.ca and I'll have a look at it for you.