Search Form and PickList Form

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
 
G

George Nicholson

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

George Nicholson

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Criteria when opening form 1
If Statements 2
Using a wildcard to open a form 2
I am not "getting" it.....DoCmd. OpenForm 4
Puzzling error with DoCmd.Close 2
Coding help 0
Pass data to new form 2
combo and text boxes to search a form 2

Top