Need help with strategy for retrieving records 1

[elff]

For this project we have a purchase order record. When the purchase order is initially created, some key information is entered such as employee id, supplier id, date, and job site information. One form has been created to enter that data. There are some additional fields in the table for entering supplier invoice information. This information isn't available at the time the purchase order is created and needs to be entered at a later time. We would like to create a separate form, where you would select the PurchaseOrder, then some of the already filled in fields will display and the invoice information fields will be in the tab order to fill in. We would like to have a pulldown list or some really easy way to select the purchase order via it's key field (PONumber). I've been able to use a command button set to "find record" and that works, but isn't as simple as a pull down list or combo box or maybe some methods I don't even know about. Does anyone have any ideas on how I could make this work? I've played with queries and every other way I could think of with no luck. Thanks!

 

[KenReay]

Hi

Make a combo box with a record source of you Purchase Order Table, slecting the unique key as first (or only) column, on a bound form (bound to the Purchae Order Table).

The combo box should not be bound.

The sample code below will retrieve the record seelcted from the combo box list, not the sample is using SupplierId, you would (probably) be using PurchaseOrder Number, but it will give you the basis of what you need to do:

Private Sub cboSupplierId_AfterUpdate()
'
Me.RecordsetClone.FindFirst "supplierid = '" & cboSupplierId & "'"
If Me.RecordsetClone.NoMatch Then
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

Note if your UNique key on the PO table is numeric rather than string, you do not need the ' around the id

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[elff]

Thank you for the suggestion. I'm trying to implement your idea, but having problems. I've a feeling I bit off a bit more than I should have in agreeing to do this project for a friend (ie: I'm an obvious non-expert in Access programming). I keep getting the message "missing operator in expression". I've a feeling I'm mixing up where double quotes should be vs. single quotes. Can you confirm what order the quotation marks are in your example, and which I'd leave out if the field is numeric? Many thanks!

 

[KenReay]

Hi

if it is a string

'
Me.RecordsetClone.FindFirst "supplierid = '" & cboSupplierId & "'"

If it is numeric

'
Me.RecordsetClone.FindFirst "supplierid = " & cboSupplierId




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK