I NEED A CODE FOR A SEARCH / FIND FROM ONE FORM TO ANOTHER.

G

Guest

I need a code that can help me to do a multiple search in an unbound form.
Where the user can choose any texbox or combo and enter the appropriate
information and it will produce all the records in the table that pertains to
that information and populate it/ them to another form called the 'Edith
Record Form".

Please this is not using one parameter textbox or combo box to do a search,
rather it requires the user choosing from any of the 57 in the form, on
entering information specific to that textbox or combo all other records
concerning that information is retrieved from the table. The idea behind this
is a situation where the user cannot remember for instance a File number he
can search with any other information handy to him.

Though, right now I have this code below which can only search with
Filenumber. I have tried to use it with the Municipality and other parameters
in the table field and it is not working what am I doing wrong and how can I
correct it.
I know there are smart people out there that can help me. Thanks in advance.

Private Sub SEARCH_RECORD_Click()
On Error GoTo Err_SEARCH_RECORD_Click

This section is working great, finding any file number entered into the text
box and populating it to the Edit Form.
Dim stDocName As String
Dim stLinkCriteria As String
'Check to make sure a correct File Number was entered
Me.FILENO.SetFocus
If Me.FILENO.Text = "" Then
MsgBox "PLEASE ENTER A VALID FILENO", vbExclamation, "ACRP"
Exit Sub
End If
'Select records from Edit Record Form that match the selection criteria of
'a chosen File Number
Me.FILENO.SetFocus
If Me.FILENO.Text = "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' "
ElseIf Me.FILENO.Text <> "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' and [FILENO] =
'" & Me.FILENO & "' "
End If

This section of the code is not working! So what is it that I'm doing
wrongly. Because I have up to 50 parameters and objects that I have to work
with. Right now it's only one working.
'Check to make sure a correct Municipality was entered
'Me.MUNICIPALITY.SetFocus
'If Me.MUNICIPALITY.Text = "" Then
' MsgBox "PLEASE ENTER A VALID Municiplaity", vbExclamation, "ACRP"
' Exit Sub
'End If
'Select records from Edit Record Form that match the selection criteria of
'a Municipal
' Me.MUNICIPALITY.SetFocus
' If Me.MUNICIPALITY.Text = "" Then
' stLinkCriteria = "[MUNICIPALITY] Like '*" & Me.STREET & "*' "
'ElseIf Me.MUNICIPALITY.Text <> "" Then
' stLinkCriteria = "[MUNICIPALITY] Like '*" & Me.STREET & "*' and
[MUNICIPLAITY] = '" & Me.MUNICIPALITY & "' "
'End If




stDocName = "EDITRECORDFORM"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_SEARCH_RECORD_Click:
Exit Sub

Err_SEARCH_RECORD_Click:
MsgBox Err.Description
Resume Exit_SEARCH_RECORD_Click

End Sub
 
D

Douglas J Steele

It would help if you indicated what "not working" means. Do you get an error
message? If so, what's the error? If not, do you get results, but not the
results you're expecting? If so, how does what you're getting differ from
what you're expecting?

What's the data type of FileNo? If it's numeric, you don't need quotes
around it:

If Me.FILENO.Text = "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' "
ElseIf Me.FILENO.Text <> "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' and [FILENO] = " &
Me.FILENO & " "
End If

Note, too, that you've got FILENO as the field name, but Me.STREET as the
criteria.

FWIW, it's not really a good idea to repeat the criteria in both parts like
that. What would be more common would be:

If Len(Me.Street & vbNullString) > 0 Then
stLinkCriteria = stLinkCriteria & _
"[STREET] Like '*" & Me.STREET & "*' AND "
End If
If Len(Me.Street & vbNullString) > 0 Then
stLinkCriteria = stLinkCriteria & _
"[FILENO] = " & Me.FILENO & " AND "
End If
If Len(Me.MUNICIPALITY & vbNullString) > 0 Then

stLinkCriteria = stLinkCriteria & _
"[MUNICIPALITY] = '*" & Me.MUNICIPALITY & "*' AND "
End If

' Now that you're done, take the last " AND " off
' the end of the string

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)
End If

The reason for concatenating vbNullString to the control name like that is
that it allows you to check for Null and zero-length strings at the same
time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Zetony said:
I need a code that can help me to do a multiple search in an unbound form.
Where the user can choose any texbox or combo and enter the appropriate
information and it will produce all the records in the table that pertains to
that information and populate it/ them to another form called the 'Edith
Record Form".

Please this is not using one parameter textbox or combo box to do a search,
rather it requires the user choosing from any of the 57 in the form, on
entering information specific to that textbox or combo all other records
concerning that information is retrieved from the table. The idea behind this
is a situation where the user cannot remember for instance a File number he
can search with any other information handy to him.

Though, right now I have this code below which can only search with
Filenumber. I have tried to use it with the Municipality and other parameters
in the table field and it is not working what am I doing wrong and how can I
correct it.
I know there are smart people out there that can help me. Thanks in advance.

Private Sub SEARCH_RECORD_Click()
On Error GoTo Err_SEARCH_RECORD_Click

This section is working great, finding any file number entered into the text
box and populating it to the Edit Form.
Dim stDocName As String
Dim stLinkCriteria As String
'Check to make sure a correct File Number was entered
Me.FILENO.SetFocus
If Me.FILENO.Text = "" Then
MsgBox "PLEASE ENTER A VALID FILENO", vbExclamation, "ACRP"
Exit Sub
End If
'Select records from Edit Record Form that match the selection criteria of
'a chosen File Number
Me.FILENO.SetFocus
If Me.FILENO.Text = "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' "
ElseIf Me.FILENO.Text <> "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' and [FILENO] =
'" & Me.FILENO & "' "
End If

This section of the code is not working! So what is it that I'm doing
wrongly. Because I have up to 50 parameters and objects that I have to work
with. Right now it's only one working.
'Check to make sure a correct Municipality was entered
'Me.MUNICIPALITY.SetFocus
'If Me.MUNICIPALITY.Text = "" Then
' MsgBox "PLEASE ENTER A VALID Municiplaity", vbExclamation, "ACRP"
' Exit Sub
'End If
'Select records from Edit Record Form that match the selection criteria of
'a Municipal
' Me.MUNICIPALITY.SetFocus
' If Me.MUNICIPALITY.Text = "" Then
' stLinkCriteria = "[MUNICIPALITY] Like '*" & Me.STREET & "*' "
'ElseIf Me.MUNICIPALITY.Text <> "" Then
' stLinkCriteria = "[MUNICIPALITY] Like '*" & Me.STREET & "*' and
[MUNICIPLAITY] = '" & Me.MUNICIPALITY & "' "
'End If




stDocName = "EDITRECORDFORM"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_SEARCH_RECORD_Click:
Exit Sub

Err_SEARCH_RECORD_Click:
MsgBox Err.Description
Resume Exit_SEARCH_RECORD_Click

End Sub
Click to expand...
 
G

Guest

It would help if you indicated what "not working" means. Do you get an error
message? If so, what's the error? If not, do you get results, but not the
results you're expecting? If so, how does what you're getting differ from
what you're expecting?

What's the data type of FileNo? If it's numeric, you don't need quotes
around it:

The things that are not working fine in this program code are these:

(1) The first part of the program was able to do exactly what I wanted, but
trying to use thesame method of code for the Municipality, Street,
ProjectName, Project_Descriptions Etc. It gave me a sytax error
(2) The result that I'm getting is limited. It is only the first part of the
code that is giving me a result. It populates the other form with its' search
result, when you enter the file number. In likewise I want to enter any of
the other field parameters in their text and get a result.
(3) FileNo is not a numeric it is text because it contains alphanumeric.
But I have other fields that are numerics that will be used in the search
criteria.
Thanks for your input. Hoping that I will get enough response that will
help me out of this.

Thanks all.

Douglas J Steele said:
It would help if you indicated what "not working" means. Do you get an error
message? If so, what's the error? If not, do you get results, but not the
results you're expecting? If so, how does what you're getting differ from
what you're expecting?

What's the data type of FileNo? If it's numeric, you don't need quotes
around it:

If Me.FILENO.Text = "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' "
ElseIf Me.FILENO.Text <> "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' and [FILENO] = " &
Me.FILENO & " "
End If

Note, too, that you've got FILENO as the field name, but Me.STREET as the
criteria.

FWIW, it's not really a good idea to repeat the criteria in both parts like
that. What would be more common would be:

If Len(Me.Street & vbNullString) > 0 Then
stLinkCriteria = stLinkCriteria & _
"[STREET] Like '*" & Me.STREET & "*' AND "
End If
If Len(Me.Street & vbNullString) > 0 Then
stLinkCriteria = stLinkCriteria & _
"[FILENO] = " & Me.FILENO & " AND "
End If
If Len(Me.MUNICIPALITY & vbNullString) > 0 Then

stLinkCriteria = stLinkCriteria & _
"[MUNICIPALITY] = '*" & Me.MUNICIPALITY & "*' AND "
End If

' Now that you're done, take the last " AND " off
' the end of the string

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)
End If

The reason for concatenating vbNullString to the control name like that is
that it allows you to check for Null and zero-length strings at the same
time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Zetony said:
I need a code that can help me to do a multiple search in an unbound form.
Where the user can choose any texbox or combo and enter the appropriate
information and it will produce all the records in the table that pertains to
that information and populate it/ them to another form called the 'Edith
Record Form".

Please this is not using one parameter textbox or combo box to do a search,
rather it requires the user choosing from any of the 57 in the form, on
entering information specific to that textbox or combo all other records
concerning that information is retrieved from the table. The idea behind this
is a situation where the user cannot remember for instance a File number he
can search with any other information handy to him.

Though, right now I have this code below which can only search with
Filenumber. I have tried to use it with the Municipality and other parameters
in the table field and it is not working what am I doing wrong and how can I
correct it.
I know there are smart people out there that can help me. Thanks in advance.

Private Sub SEARCH_RECORD_Click()
On Error GoTo Err_SEARCH_RECORD_Click

This section is working great, finding any file number entered into the text
box and populating it to the Edit Form.
Dim stDocName As String
Dim stLinkCriteria As String
'Check to make sure a correct File Number was entered
Me.FILENO.SetFocus
If Me.FILENO.Text = "" Then
MsgBox "PLEASE ENTER A VALID FILENO", vbExclamation, "ACRP"
Exit Sub
End If
'Select records from Edit Record Form that match the selection criteria of
'a chosen File Number
Me.FILENO.SetFocus
If Me.FILENO.Text = "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' "
ElseIf Me.FILENO.Text <> "" Then
stLinkCriteria = "[FILENO] Like '*" & Me.STREET & "*' and [FILENO] =
'" & Me.FILENO & "' "
End If

This section of the code is not working! So what is it that I'm doing
wrongly. Because I have up to 50 parameters and objects that I have to work
with. Right now it's only one working.
'Check to make sure a correct Municipality was entered
'Me.MUNICIPALITY.SetFocus
'If Me.MUNICIPALITY.Text = "" Then
' MsgBox "PLEASE ENTER A VALID Municiplaity", vbExclamation, "ACRP"
' Exit Sub
'End If
'Select records from Edit Record Form that match the selection criteria of
'a Municipal
' Me.MUNICIPALITY.SetFocus
' If Me.MUNICIPALITY.Text = "" Then
' stLinkCriteria = "[MUNICIPALITY] Like '*" & Me.STREET & "*' "
'ElseIf Me.MUNICIPALITY.Text <> "" Then
' stLinkCriteria = "[MUNICIPALITY] Like '*" & Me.STREET & "*' and
[MUNICIPLAITY] = '" & Me.MUNICIPALITY & "' "
'End If




stDocName = "EDITRECORDFORM"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_SEARCH_RECORD_Click:
Exit Sub

Err_SEARCH_RECORD_Click:
MsgBox Err.Description
Resume Exit_SEARCH_RECORD_Click

End Sub
Click to expand...
Click to expand...
 
J

John Vinson

I need a code that can help me to do a multiple search in an unbound form.
Where the user can choose any texbox or combo and enter the appropriate
information and it will produce all the records in the table that pertains to
that information and populate it/ them to another form called the 'Edith
Record Form".
Click to expand...

Have you ruled out using the (really very capable) built-in Query By
Form approach?

John W. Vinson[MVP]
 

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

search command 2
Multiple Search Command criteria in a form 2
Open another subform from a subform 1
Open a form and close one 1
Trying to get Current User info only 2
finding record from autonumber field 7
Can't find error 1
Code to open a query 2

Top