Hi, I have in my database a form

[firsttime]

Hi,

I have in my database a form that is called Product. (with productid,productname, product description,unit price,in stock etc...). Everytime i want to see all the information from one product, i have to use the navigation bar (record selector)to select the product.It takes a lot of time to go through all the products to get the one i want.

What i want is to make the box 'productname' into a combobox so that i can select from it, and to see all the information from that particular productname too. But i don't the query or the expression for it.
Can anybody help me!!!!

Thx in advance

 

[SeeJane]

Hi,

Yup, you can do this. If I were you, I'd create a combo box as you describe, then put a command button "Find Record" (cmdFindRec) next to it. This way, a user won't accidentally scroll up to the combo and navigate around by mistake. To prepare for the possibility that you may have identical product names at some point, I'd also have a "Find Next" command button (cmdFindNext - have it default to disabled when you open the form)

Here's some code to try. In this example, the combo box is cboProducts, the field to seatch is ProductName, "Find Record" is cmdFindRec and "Find Next" is cmdFindNext. Both command buttons will get their own code snippet. Make sure you have the DAO reference turned on before you run it.

For the OnClick event of cmdFindRec...

Dim rsProduct As DAO.Database

'define clone of form's recordset
rsProduct = Me.RecordsetClone

'search for value in combo box in the field ProductName
rsProduct.FindFirst "ProductName = '" & Me.cboProduct & "'"

'If you get a match, go to matching record, enable find next
If Not rsProduct.NoMatch Then
     Me.Bookmark = rsProduct.Bookmark
     Me.cmdFindNext.Enabled = True
'Message if there isn't a match
Else
     MsgBox "Sorry, no records"
End If

'Clean up
rsProduct.Close
Set rsProduct = Nothing

Next, put this in the OnClick event of cmdFindNext...

Dim rsProduct As DAO.Database

'define clone of form's recordset
rsProduct = Me.RecordsetClone

'search for value in combo box in the field ProductName
rsProduct.FindNext "ProductName = '" & Me.cboProduct & "'"

'If you get a match, go to matching record
If Not rsProduct.NoMatch Then
     Me.Bookmark = rsProduct.Bookmark
'Message if there isn't a match
Else
     MsgBox "Already on last match"
End If

'Clean up
rsProduct.Close
Set rsProduct = Nothing

Good luck!
CJ

 

[firsttime]

I tried to use this code but i get the error:
"user-defined type not defined". And what do you mean by ;
'Make sure you have the DAO reference turned on before you run it.'

I am new at this, could you be more specific!!

The name of the box product-id=ProductId (autonumber)
the name of the combobox productname=NaamProduct

(the productid is related with the productname, every productname has an unique number. So if i selected a productname i want the productid change to and all the value in the form that is related to the productname)

Anyone else too that can help me???

THX IN ADVANCE