Running a qury in code and placing data in lst box

S

Scott

I am using a command button to search for a particular record. Then when it
finds the record I want it to run a qry that pulls information about the
record from another talbe and place that information in a lst box on a page.
 
S

Scott

With that code it is placing my data in the text boxes on the screen instead
of the list box.





Ofer said:
Try this

Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,[Resident License
State], [Resident license Type], [Resident License Expire] From [tblResident
License] Where SSN = '" & [RetVal] & "'"
Me.stCurrentResidentLicense.Requery

End Sub

Scott said:
Ofer could you take a look at this and tell me what you think.

'Will be used to locate Producer Resident License Information. And will
place information in list box on the
'License information page of the Producer information form
Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,Resident License
State, Resident license Type, Resident License Expire From tblResident
License Where"
SSN = "&[RetVal&"""

The Run
Me.stCurrentResidentLicense.Requery

End Sub


Ofer said:
After you retrieve the record you can run this code

' If the record is number
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = " & [Record Found]

' If the record is string
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = '" & [Record Found] & "'"

Then run
Me.ListBoxName.Requery

:

I am using a command button to search for a particular record. Then when it
finds the record I want it to run a qry that pulls information about the
record from another talbe and place that information in a lst box on a page.
Click to expand...
Click to expand...
Click to expand...
 
S

Scott

Ofer could you take a look at this and tell me what you think.

'Will be used to locate Producer Resident License Information. And will
place information in list box on the
'License information page of the Producer information form
Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,Resident License
State, Resident license Type, Resident License Expire From tblResident
License Where"
SSN = "&[RetVal&"""

The Run
Me.stCurrentResidentLicense.Requery

End Sub


Ofer said:
After you retrieve the record you can run this code

' If the record is number
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = " & [Record Found]

' If the record is string
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = '" & [Record Found] & "'"

Then run
Me.ListBoxName.Requery

Scott said:
I am using a command button to search for a particular record. Then when it
finds the record I want it to run a qry that pulls information about the
record from another talbe and place that information in a lst box on a page.
Click to expand...
Click to expand...
 
O

Ofer

After you retrieve the record you can run this code

' If the record is number
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = " & [Record Found]

' If the record is string
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = '" & [Record Found] & "'"

Then run
Me.ListBoxName.Requery
 
O

Ofer

Try this

Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,[Resident License
State], [Resident license Type], [Resident License Expire] From [tblResident
License] Where SSN = '" & [RetVal] & "'"
Me.stCurrentResidentLicense.Requery

End Sub

Scott said:
Ofer could you take a look at this and tell me what you think.

'Will be used to locate Producer Resident License Information. And will
place information in list box on the
'License information page of the Producer information form
Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,Resident License
State, Resident license Type, Resident License Expire From tblResident
License Where"
SSN = "&[RetVal&"""

The Run
Me.stCurrentResidentLicense.Requery

End Sub


Ofer said:
After you retrieve the record you can run this code

' If the record is number
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = " & [Record Found]

' If the record is string
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = '" & [Record Found] & "'"

Then run
Me.ListBoxName.Requery

Scott said:
I am using a command button to search for a particular record. Then when it
finds the record I want it to run a qry that pulls information about the
record from another talbe and place that information in a lst box on a page.
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Ofer said:
After you retrieve the record you can run this code

' If the record is number
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = " & [Record Found]

' If the record is string
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = '" & [Record Found] & "'"

Then run
Me.ListBoxName.Requery
Click to expand...

You don't have to explicitly requery the list box. If you change the
RowSource property of a list or combo box, the control is automatically
requeried.
 
O

Ofer

By adding this code
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True

You ask the form fields to display the specific record, if you dont want the
form records to change, remove this lines


Scott said:
With that code it is placing my data in the text boxes on the screen instead
of the list box.





Ofer said:
Try this

Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,[Resident License
State], [Resident license Type], [Resident License Expire] From [tblResident
License] Where SSN = '" & [RetVal] & "'"
Me.stCurrentResidentLicense.Requery

End Sub

Scott said:
Ofer could you take a look at this and tell me what you think.

'Will be used to locate Producer Resident License Information. And will
place information in list box on the
'License information page of the Producer information form
Private Sub cmdFindProducerResidentLicenseInformation_Click()
Dim RetVal As String

RetVal = InputBox("Enter SSN")

If RetVal <> "" Then
Me.Filter = "[tblResident License_SSN] = '" & RetVal & "'"
Me.FilterOn = True
End If
' Need this section to place the data in the list box instead of the txt
boxes on the page.
Me.lstCurrentResidentLicense.RowSource = "Select SSN,Resident License
State, Resident license Type, Resident License Expire From tblResident
License Where"
SSN = "&[RetVal&"""

The Run
Me.stCurrentResidentLicense.Requery

End Sub


:

After you retrieve the record you can run this code

' If the record is number
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = " & [Record Found]

' If the record is string
Me.ListBoxName.RowSource = "Select Field1, Field2 From TableName Where
Field1 = '" & [Record Found] & "'"

Then run
Me.ListBoxName.Requery

:

I am using a command button to search for a particular record. Then when it
finds the record I want it to run a qry that pulls information about the
record from another talbe and place that information in a lst box on a page.
Click to expand...
Click to expand...
Click to expand...
 
Top