Find a specifc record in a list box (2002)

Topic

New Reply

I have a combo box (cboShip) that is used to select Ship To: (Vendors)
When a vendor is selected a calculated expression in 98050-qryVendorsAddressCalcLookup posts it’s address, city, state and zip with = [cboShipTo].[Column](2) into ShipToAddressField.
Sometimes there is incomplete address information i.e. Sacramento, is posted instead of Sacramento, CA
because CA was not originally posted.
I have a label &Ship To: that puts me back in the Vendor entry form.
On the Vendors form is a list box lstVendors. I want to be able to go from the combo box on invoice form
and go directly to it’s specific record, select it and make the form the current record to make corrections.
Frank

Reply | Quote

Replies

I think that I understand what you want to do. I use the following code to open a second form to the record specified by the key on the first form:

Private Sub cmdGoToSource_Click()
'Source Picklist: open frmSourceEdit for record

On Error GoTo Err_cmdGoToSource_Click

    Dim stDocName As String
    Dim stLinkCriteria As String


    stDocName = "frmSourceEdit"
    
    stLinkCriteria = "[SourceID]=" & Me![SourceID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_cmdGoToSource_Click:
    Exit Sub


Err_cmdGoToSource_Click:
    MsgBox Err.Description
    Resume Exit_cmdGoToSource_Click
    
End Sub

You will need to change the FormName to your Edit form, the cmdName to your CommandButton and the stLinkCriteria statement to your key and ListboxName (maybe with a column number, depending on what fields are in that Listbox.

Reply | Quote

HI Thomas,
Works Great!
Am I wrong for asking for more?
The form is syncronized but the list box highlighted row is at the top and not say on the forth row.
How can you get it to also syncronize with the list box?
Frank

Reply | Quote

Glad that worked for you, but I’m not able to visualize what you are describing this time. Are you referring to a listbox on the edit form that was synchronized? What is the purpose of that listbox if you are all ready on the correct record?

Reply | Quote

Well the lstVendors is a listbox that when a row is selected the form displays the contents of that record.
Just a simple list box on a form.
I want the row also to be selected.
Frank

Reply | Quote

But you just said the row *was* selected. and then the record was displayed, so what exactly are you trying to do?

Reply | Quote

The top most row is selected always and now the exact row.
Would be nice if it also goes to the exact row the corresponds to the record in the form which is ok.
Frank

Reply | Quote

Frank, It might help us have a better idea of what you’re doing if you could post a screenshot of the form (s) you’re working with.

Reply | Quote

Hi Mark,
I’m sorry if I am not communicating what I’d like to do.
How do you do a screen shot and post it to the Lounge.
In the mean time.
I have two forms: Sales Invoice and Vendor Entry Form.
The Vendor Entry form has a list box of vendors-lstVendors and various fields vendor name etc.
Selecting a row on the list box lets say row 5 brings up record 5 on this form with vendor 5.
On the Sales Invoice for is a combo box that has these vendors as ship to. If in the address field the data
is incomplete I click a link and it goes to Vendor Entry Form to update the data. Your code links me up correctly with the vendor but on lstVendors on the form always is on the first row rather then say row 5 if the vendor was vendor 5.
Frank

Reply | Quote

To make a screenshot of the current window you can press ALT + PrintScreen (this copies an image to the clipboard), then open any graphics editor (such as Paint) and paste. Then crop the image to show only the relevant portion and save the image as a file – usually GIF is a good format, but if it’s big you’ll probably want to use JPG. Then use the file attach controls at the bottom of the New Post window to attach your file (Browse…). As the instructions (in RED) indicate, do this LAST or it won’t show up.

If you want to test this out you can use the Test Area forum to practice.

As for your current problem, I don’t understand what you mean by “links me up correctly with the vendor” – do you mean it opens the vendor form to the correct record? Is the vendor form a Datasheet or does it display only ONE vendor? If it is a Datasheet, then you may consider using the Filter argument of the OpenForm method/macro. Have it filter to show only the desired vendor.

Hope this helps

Reply | Quote

Tried to post screen shots got this message will try later.
Frank

Reply | Quote

Can’t send Vendor Form Snapshot have tried several times 500 internal server error.
Frank

Reply | Quote