Find a Record & display it on a Form!!

[oldblindpew]

Okay, I've been working at this for several days. I just want to find a
record and display it on a form for editing.

I was using a list box on a dialog form to allow the user to pick the
appropriate record by FirmName. The critical command is DoCmd.FindRecord. I
think this command works by finding a record in which the FirmName value
matches the value supplied via the ListBox. And I think that the form being
bound to the Firms table enables all the fields to be displayed. It works
just fine until you get duplicate firm names, then, no matter which one the
user picks via the dialog box, Access finds only the first instance in the
table.

How do I tell DoCmd.FindRecord to search on the FirmID rather than the
FirmName?

Thanks

 

[Mr. B]

Hi, oldblindpew

To get the FirmId you cna add the FirmId field to your ListBox as the bound
field, but only display the Firm Name. You do this by specifying the column
count as 2 but then specify the first column width as zero and then a width
for the second column.

I am troubled by the fact that you have multiple entries with the same firm
name. If you want you users to select the correct firm, you will need to
also display something in your list box that is unigue for each of the firms
with the same name, else users will not be able to locate the correct one.
Just a thought.

If you want or need more assistance, please just post back.

Mr. B

 

[oldblindpew]

Yes, I know. My listbox RowSource specifies the FirmID, FirmName, and
FirmCity, ordered by FirmName. FirmID is hidden. User can see both FirmName
and FirmCity, so he knows which firm to pick. I know also that the value of
the listbox is determined by the BoundColumn setting. What I do not know or
understand, is how to write the code in my event procedure to get VBA to find
the record and display it on the main form. I had been using these bits of
code, where lstFirm is the name of the ListBox:

Dim strSelect As String
strSelect = Me!lstFirm
DoCmd.FindRecord strSelect

I tried changing the string variable to an integer variable, but it didn't
work, i.e., it didn't put the record on the form. The main form remains
unchanged, as if DoCmd.FindRecord did not find a match.

 

[Mr. B]

oldblinkpew,

Sorry if I only suggested things that you already knew. I just did not know
what you needed.

You can use the After Update event of your listbox to perform the operation.

After you have acquired the FirmID number from your listbox, you can then
just open your other form to that specific record by use the Where paramiter
when opening the form.

Your after update event would have code like this:

'declare a variable to hold the selected FirmId
Dim lngFirmId as long

'assign the selected value to the variable
lngFirmID = me.YourListBoxNameHere

'open the form with all records in it
docmd.OpenForm "NameOfYourFormHere", , , "FirmID = " & lngFirmID & ""

The Where part of the Openform command is simply the name of the file equal
to the selected value from your listbox.

HTH
Mr. B
(askdoctoraccess dot com)

 

[oldblindpew]

Mr. B,
Thanks for your reply.
The form we are speaking of is already open.
Here's the flow: The user opens the form for firm maintenance. One of the
controls on this form is a textbox called txbFirmName, which displays the
name of the firm. Adjacent to this textbox is a command button with a
picture of binoculars on it. When you click on the button, a dialog form is
opened containing a list box showing all the firms in alphabetical order.
The user finds the firm he is interested in, and selects it. At this point
the dialog box closes and the selected firm's record is supposed to appear on
the underlying form, but this last bit doesn't work.
Just before the DoCmd.FindRecord is executed, the dialog form is closed
(DoCmd.Close), and the cursor or whatever has been moved from the binoculars
button back to the txbFirmName (DoCmd.GoToControl "txbFirmName").
So we're parked on the txbFirmName when DoCmd.FindRecord executes. It's
like Access is so fixated on WHAT things are and WHERE things are (object
based) that it can't see what it is we are DOING (procedural). I think it is
trying to find a record whose FirmName value matches the FirmID number, and
of course, there is no such record. I suspect I need a different command
than FindRecord.