O
oldblindpew
What kind of a database cannot retrieve a record and show it on a form?
Here's the flow: The user opens a form for Firms maintenance. On this form
is a textbox which displays the name of the firm. Next 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 appears on the underlying form, but this last bit doesn't work for
duplicate firm names. Access retrieves only the first instance of the
selected firm, regardless of which instance the user selects. Here is the
code behind the event:
Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.
Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm
' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If
' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm
' Close the dialog form to switch back to the main form.
DoCmd.Close
' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't.
DoCmd.GoToControl "txbFirmName"
' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect
' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"
Exit_OkFindFirm:
On Error Resume Next
Exit Sub
Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm
End Sub
Originally, I put the firm's name into a string variable (strSelect) and did
DoCmd.FindRecord strSelect. This works, but not for duplicate firm names.
Next I tried passing the firm's unique ID number to a long integer variable.
This did not work at all, doubtless because FindRecord was searching the
firm name field for the firm ID number, which of course isn't there.
Next, I tried to force FindRecord to look in all fields for a match to the
firm ID. I didn't much care for this approach, but it didn't work anyway, so
that was okay.
The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the Firms table
wasn't open.
My brain hurts.
Here's the flow: The user opens a form for Firms maintenance. On this form
is a textbox which displays the name of the firm. Next 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 appears on the underlying form, but this last bit doesn't work for
duplicate firm names. Access retrieves only the first instance of the
selected firm, regardless of which instance the user selects. Here is the
code behind the event:
Option Compare Database 'Use database order for string comparisons.
Option Explicit 'Requires variables be declared before use.
Private Sub cmdOkFindFirm_Click()
' Find record for main form based on selection in dialog form.
On Error GoTo Err_OkFindFirm
' Check to see if no selection was made.
If IsNull(Me!lstFirm) Then
MsgBox "Make a selection or click Cancel", , "No Selection"
GoTo Exit_OkFindFirm
End If
' Store the selection in a variable.
Dim lngSelect As Long
lngSelect = Me!lstFirm
'Dim strSelect As String
'strSelect = Me!lstFirm
' Close the dialog form to switch back to the main form.
DoCmd.Close
' Move the cursor back to the search field.
' SendKeys "+{TAB}" doesn't work, although I see no reason why it shouldn't.
DoCmd.GoToControl "txbFirmName"
' Find the selected record.
'DoCmd.FindRecord lngSelect, , , , , acAll
'DoCmd.FindRecord strSelect
DoCmd.GoToRecord acDataForm, "tblFirm", acGoTo, lngSelect
' Move the cursor back to the search button.
DoCmd.GoToControl "cmdSearchFirm"
Exit_OkFindFirm:
On Error Resume Next
Exit Sub
Err_OkFindFirm:
MsgBox "Error #: " & Err & Chr(13) & Err.Description
Resume Exit_OkFindFirm
End Sub
Originally, I put the firm's name into a string variable (strSelect) and did
DoCmd.FindRecord strSelect. This works, but not for duplicate firm names.
Next I tried passing the firm's unique ID number to a long integer variable.
This did not work at all, doubtless because FindRecord was searching the
firm name field for the firm ID number, which of course isn't there.
Next, I tried to force FindRecord to look in all fields for a match to the
firm ID. I didn't much care for this approach, but it didn't work anyway, so
that was okay.
The most recent thing I tried was passing the firm Id number to
DoCmd.GoToRecord. This gave me an error message stating that the Firms table
wasn't open.
My brain hurts.