Search Form and PickList Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a created two forms. The first form is the search form. The search
form uses two text boxes that can be used to search the database via either a
last name or a Party Number.

The Last Name is obviously a string. The Party Number was previously set up
as a string, but I have changed it to a Number (long type).

Since the change of the PartyNumber from a string to a number (long type),
the picklist either will open blank or will create errors.

If I enter in a Last Name (string), I get the error “2465: can’t find the
PartyNumber referenced in your expressionâ€

Any help is appreciated….


Below is the code attached to each of the forms:

frmSEARCH:

Option Compare Database
Private Sub LastName_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDEFENDANTSmain"
stLinkCriteria = "[LastName] like '" & "*" & Me.LastName & "*'"

If DCount("*", "taDefendant", stLinkCriteria) > 0 Then
DoCmd.openFORM _
FormName:="frmPICKLIST", _
OpenArgs:=stLinkCriteria
Else
MsgBox "There is no Defendant with this LAST name in the database."
End If

End Sub

Private Sub PartyNumber_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As Long

stDocName = "frmDEFENDANTSmain"
stLinkCriteria = [PartyNumber] = Me!PartyNumber

If DCount("*", "taDefendant", stLinkCriteria) > 0 Then
DoCmd.openFORM _
FormName:="frmPICKLIST", _
OpenArgs:=stLinkCriteria
Else
MsgBox "There is no Defendant with this PARTY NUMBER in the
database."
End If

End Sub



frmPICKLIST

Option Compare Database

Private Sub Form_Load()
With Me
.NavigationButtons = False
.RecordSelectors = False
End With
With Names
.RowSource = "SELECT [PartyNumber],[LastName],[FirstName], [MiddleName],
[Suffix] FROM taDefendant WHERE " & Me.OpenArgs & "ORDER BY LastName,
Firstname"
.ColumnCount = 5
.ColumnWidths = "1 in;1.25 in; 1.25 in; 1.25 in; .75 in"
.ColumnHeads = True
.Requery
End With

' Closes Search Form
'
DoCmd.Close acForm, "frmSEARCH"
DoCmd.Close acForm, "frmDEFENDANTSmain"

End Sub

Private Sub Names_DblClick(Cancel As Integer)
Dim F As Form
Dim R As DAO.Recordset

DoCmd.openFORM "frmDEFENDANTSmain"
Set F = Forms![frmDEFENDANTSmain]
Set R = F.RecordsetClone
R.FindFirst "[taDEFENDANT].[PartyNumber] = '" & Me!PartyNumber & "'"
F.Bookmark = R.Bookmark
DoCmd.Close acForm, Me.Name

End Sub
 
stLinkCriteria = [PartyNumber] = Me!PartyNumber
s/b stLinkCriteria = "[PartyNumber] =" & Me!PartyNumber
- add the missing quotes
...."ORDER BY LastName, Firstname"
Click to expand...
s/b ...." ORDER BY LastName, Firstname"
- add the missing space (this may be corrected aautomatically but just in
case...)
R.FindFirst "[taDEFENDANT].[PartyNumber] = '" & Me!PartyNumber & "'"
Click to expand...
s/b R.FindFirst "[PartyNumber] = " & Me!PartyNumber
- 1) There is no need to specify a table since Fieldname *must* exist within
R. Remove the conflicting table reference.
- 2) remove the single quotes since PartyNumber is no longer text

HTH,


UKC said:
I have a created two forms. The first form is the search form. The
search
form uses two text boxes that can be used to search the database via
either a
last name or a Party Number.

The Last Name is obviously a string. The Party Number was previously set
up
as a string, but I have changed it to a Number (long type).

Since the change of the PartyNumber from a string to a number (long type),
the picklist either will open blank or will create errors.

If I enter in a Last Name (string), I get the error "2465: can't find the
PartyNumber referenced in your expression"

Any help is appreciated..


Below is the code attached to each of the forms:

frmSEARCH:

Option Compare Database
Private Sub LastName_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDEFENDANTSmain"
stLinkCriteria = "[LastName] like '" & "*" & Me.LastName & "*'"

If DCount("*", "taDefendant", stLinkCriteria) > 0 Then
DoCmd.openFORM _
FormName:="frmPICKLIST", _
OpenArgs:=stLinkCriteria
Else
MsgBox "There is no Defendant with this LAST name in the
database."
End If

End Sub

Private Sub PartyNumber_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As Long

stDocName = "frmDEFENDANTSmain"
stLinkCriteria = [PartyNumber] = Me!PartyNumber

If DCount("*", "taDefendant", stLinkCriteria) > 0 Then
DoCmd.openFORM _
FormName:="frmPICKLIST", _
OpenArgs:=stLinkCriteria
Else
MsgBox "There is no Defendant with this PARTY NUMBER in the
database."
End If

End Sub



frmPICKLIST

Option Compare Database

Private Sub Form_Load()
With Me
.NavigationButtons = False
.RecordSelectors = False
End With
With Names
.RowSource = "SELECT [PartyNumber],[LastName],[FirstName],
[MiddleName],
[Suffix] FROM taDefendant WHERE " & Me.OpenArgs & "ORDER BY LastName,
Firstname"
.ColumnCount = 5
.ColumnWidths = "1 in;1.25 in; 1.25 in; 1.25 in; .75 in"
.ColumnHeads = True
.Requery
End With

' Closes Search Form
'
DoCmd.Close acForm, "frmSEARCH"
DoCmd.Close acForm, "frmDEFENDANTSmain"

End Sub

Private Sub Names_DblClick(Cancel As Integer)
Dim F As Form
Dim R As DAO.Recordset

DoCmd.openFORM "frmDEFENDANTSmain"
Set F = Forms![frmDEFENDANTSmain]
Set R = F.RecordsetClone
R.FindFirst "[taDEFENDANT].[PartyNumber] = '" & Me!PartyNumber & "'"
F.Bookmark = R.Bookmark
DoCmd.Close acForm, Me.Name

End Sub
Click to expand...
 
Thanks George. I'm still having a problem.

I changed this line to:

R.FindFirst "[PartyNumber]=" & Me!PartyNumber

When I run the search, I get the following error:
run-time error: 2465: MS can't find the field "PartyNumber' referred to in
your expression.

On the form_Load event I have, so, PartyNumber should be in the recordset.

With Names
.RowSource = "SELECT [PartyNumber],[LastName],[FirstName], [MiddleName],
[Suffix] FROM taDefendant WHERE " & Me.OpenArgs
.ColumnCount = 5
.ColumnWidths = "1 in;1.25 in; 1.25 in; 1.25 in; .75 in"
.ColumnHeads = True
.Requery

George Nicholson said:
stLinkCriteria = [PartyNumber] = Me!PartyNumber
Click to expand...
s/b stLinkCriteria = "[PartyNumber] =" & Me!PartyNumber
- add the missing quotes
...."ORDER BY LastName, Firstname"
Click to expand...
s/b ...." ORDER BY LastName, Firstname"
- add the missing space (this may be corrected aautomatically but just in
case...)
Click to expand...
 
Nevermind! I transposed some letters as I was typing...

Thanks for the help George.....

UKC said:
Thanks George. I'm still having a problem.

I changed this line to:

R.FindFirst "[PartyNumber]=" & Me!PartyNumber

When I run the search, I get the following error:
run-time error: 2465: MS can't find the field "PartyNumber' referred to in
your expression.

On the form_Load event I have, so, PartyNumber should be in the recordset.

With Names
.RowSource = "SELECT [PartyNumber],[LastName],[FirstName], [MiddleName],
[Suffix] FROM taDefendant WHERE " & Me.OpenArgs
.ColumnCount = 5
.ColumnWidths = "1 in;1.25 in; 1.25 in; 1.25 in; .75 in"
.ColumnHeads = True
.Requery

George Nicholson said:
stLinkCriteria = [PartyNumber] = Me!PartyNumber
Click to expand...
s/b stLinkCriteria = "[PartyNumber] =" & Me!PartyNumber
- add the missing quotes
...."ORDER BY LastName, Firstname"
Click to expand...
s/b ...." ORDER BY LastName, Firstname"
- add the missing space (this may be corrected aautomatically but just in
case...)
Click to expand...
Click to expand...
 
Sorry, you want to search for the current value of the control
R.FindFirst "[PartyNumber]=" & Me!PartyNumber
Click to expand...

Won't do that. Me!PartyNumber probably doesn't exist. Me is the Form where
the code is running - and that form doesn't have a field called PartyNumber
in it's recordset (hence the error). Even if Me referred to Names, you can't
refer to a combo/listbox column by its name.

Therefore, that line should probably be something like

R.FindFirst "[PartyNumber]=" & Clng(Me.Names.Column(0))

or (if PartyNumber is the bound field of the control)

R.FindFirst "[PartyNumber]=" & clng(Me.Names)


HTH,


UKC said:
Thanks George. I'm still having a problem.

I changed this line to:

R.FindFirst "[PartyNumber]=" & Me!PartyNumber

When I run the search, I get the following error:
run-time error: 2465: MS can't find the field "PartyNumber' referred to
in
your expression.

On the form_Load event I have, so, PartyNumber should be in the recordset.

With Names
.RowSource = "SELECT [PartyNumber],[LastName],[FirstName],
[MiddleName],
[Suffix] FROM taDefendant WHERE " & Me.OpenArgs
.ColumnCount = 5
.ColumnWidths = "1 in;1.25 in; 1.25 in; 1.25 in; .75 in"
.ColumnHeads = True
.Requery

George Nicholson said:
stLinkCriteria = [PartyNumber] = Me!PartyNumber
Click to expand...
s/b stLinkCriteria = "[PartyNumber] =" & Me!PartyNumber
- add the missing quotes
...."ORDER BY LastName, Firstname"
Click to expand...
s/b ...." ORDER BY LastName, Firstname"
- add the missing space (this may be corrected aautomatically but just in
case...)
Click to expand...
Click to expand...
 
Back
Top