Select Record Code Problem

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a form named frmAutoPayrollReport which has a combo box on it used
to select a Customer from
The Combo box is Named Combo187
I also have a form named BlankChecks that has as it's record source a table
name tblchecks.(with a field in it named customers)
The idea is too make the form BlankChecks open and show what choice I pick
from the form frmAutoPayrollReport (Combo187) List.
The Combo187 uses as it's record source a table named Customers with the
control customers.
Whereas the form BlankChecks uses as it's record source for the Customer
drop down list the table Employees, but it's control is Customers from the
table
tblchecks which has a customer field in it.
Here is my code I am trying, it does not work, opens the form, but does not
display a record.
The form BlankChecks normally opens to a new record.

Dte1 = Forms!frmAutoPayrollReport!BeginDate
Dte2 = Forms!frmAutoPayrollReport!EndDate
Dim stDocName As String
Dim stLinkCriteria As String
On Error GoTo Err_frmAutoPayrollReport_Click

Forms!frmAutoPayrollReport!Combo187 = Null
stDocName = "BlankChecks"
stLinkCriteria = "[tblchecks]=" & Me![Combo187]

DoCmd.OpenForm "BlankChecks", , , "[Customers] = '" & Me.Combo187 & "'"

Exit_frmAutoPayrollReport_Click:
Exit Sub

Err_frmAutoPayrollReport_Click:
MsgBox Err.Description
Resume Exit_frmAutoPayrollReport_Click
 
Dave,

Try stepping through the code. It looks as though you're easing the value
of the combo box before you retrieve the value. (The line just after the On
Error statement.)

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress
 
Tried different approach but it does not work either.
BlankChecks is form to open from form frmautopayrollreport
table tblchecks customer field links to table customers
linking from table customers customers field and from table tblchecks
customers field.


Dte1 = Forms!frmAutoPayrollReport!BeginDate
Dte2 = Forms!frmAutoPayrollReport!EndDate
Dim stDocName As String
Dim stLinkCriteria As String
Dim VarX As Variant

stDocName = "BlankChecks" ' Form To Open
stLinkCriteria = "[Customers]=" & Me![Combo187] ' Assuming this
references table Customers and Combo187 Value
VarX = DLookup("' & [Customers] & '", "tblchecks", "[Customers] =
Forms![BlankChecks]![Customers]")' Customer table;tblchecks;control
customers form/ etc...
DoCmd.Close acForm, Me.Name
If IsNull(VarX) Then
MsgBox "No Customer Was Found"
Else: DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_frmAutoPayrollReport_Click:
Exit Sub

Err_frmAutoPayrollReport_Click:
MsgBox Err.Description
Resume Exit_frmAutoPayrollReport_Click
End Sub




Rebecca Riordan said:
Dave,

Try stepping through the code. It looks as though you're easing the value
of the combo box before you retrieve the value. (The line just after the On
Error statement.)

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

Dave said:
I have a form named frmAutoPayrollReport which has a combo box on it used
to select a Customer from
The Combo box is Named Combo187
I also have a form named BlankChecks that has as it's record source a table
name tblchecks.(with a field in it named customers)
The idea is too make the form BlankChecks open and show what choice I pick
from the form frmAutoPayrollReport (Combo187) List.
The Combo187 uses as it's record source a table named Customers with the
control customers.
Whereas the form BlankChecks uses as it's record source for the Customer
drop down list the table Employees, but it's control is Customers from the
table
tblchecks which has a customer field in it.
Here is my code I am trying, it does not work, opens the form, but does not
display a record.
The form BlankChecks normally opens to a new record.

Dte1 = Forms!frmAutoPayrollReport!BeginDate
Dte2 = Forms!frmAutoPayrollReport!EndDate
Dim stDocName As String
Dim stLinkCriteria As String
On Error GoTo Err_frmAutoPayrollReport_Click

Forms!frmAutoPayrollReport!Combo187 = Null
stDocName = "BlankChecks"
stLinkCriteria = "[tblchecks]=" & Me![Combo187]

DoCmd.OpenForm "BlankChecks", , , "[Customers] = '" & Me.Combo187 & "'"

Exit_frmAutoPayrollReport_Click:
Exit Sub

Err_frmAutoPayrollReport_Click:
MsgBox Err.Description
Resume Exit_frmAutoPayrollReport_Click
Click to expand...
Click to expand...
 
Dave,

I made a few changes to you code. Hopefully it will get you moving forward
again.
From the code snippet it looks loke you are using the OnClick event of the
form frmAutoPayrollReport. Maybe it would be better to have the code in a
button OnClick event?

Anyway, try this:
'------------------
'are Dte1 and Dte2 dimmed as Date?
' on form frmAutoPayrollReport so can use 'Me.' syntax
Dte1 = Me.BeginDate
Dte2 = Me.EndDate

Dim stDocName As String
Dim stLinkCriteria As String

' looks like the OnClick event of the form??
On Error GoTo Err_frmAutoPayrollReport_Click

'You are setting Combo187 to NULL ?????
' then opening BlankChecks with CustomerNo = NULL???
'Forms!frmAutoPayrollReport!Combo187 = Null

stDocName = "BlankChecks"

'if Combo187 is string then use the next line
'stLinkCriteria = "[Customers] = '" & Me.Combo187 & "'"

'else if Combo187 is numeric then use the next line
stLinkCriteria = "[Customers] = " & Me.Combo187

DoCmd.OpenForm stDocName , , , stLinkCriteria

Exit_frmAutoPayrollReport_Click:
Exit Sub

Err_frmAutoPayrollReport_Click:
MsgBox Err.Description
Resume Exit_frmAutoPayrollReport_Click
'------------------

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Dave Elliott said:
Tried different approach but it does not work either.
BlankChecks is form to open from form frmautopayrollreport
table tblchecks customer field links to table customers
linking from table customers customers field and from table tblchecks
customers field.


Dte1 = Forms!frmAutoPayrollReport!BeginDate
Dte2 = Forms!frmAutoPayrollReport!EndDate
Dim stDocName As String
Dim stLinkCriteria As String
Dim VarX As Variant

stDocName = "BlankChecks" ' Form To Open
stLinkCriteria = "[Customers]=" & Me![Combo187] ' Assuming this
references table Customers and Combo187 Value
VarX = DLookup("' & [Customers] & '", "tblchecks", "[Customers] =
Forms![BlankChecks]![Customers]")' Customer table;tblchecks;control
customers form/ etc...
DoCmd.Close acForm, Me.Name
If IsNull(VarX) Then
MsgBox "No Customer Was Found"
Else: DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_frmAutoPayrollReport_Click:
Exit Sub

Err_frmAutoPayrollReport_Click:
MsgBox Err.Description
Resume Exit_frmAutoPayrollReport_Click
End Sub




Rebecca Riordan said:
Dave,

Try stepping through the code. It looks as though you're easing the value
of the combo box before you retrieve the value. (The line just after the On
Error statement.)

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

Dave said:
I have a form named frmAutoPayrollReport which has a combo box on it used
to select a Customer from
The Combo box is Named Combo187
I also have a form named BlankChecks that has as it's record source a table
name tblchecks.(with a field in it named customers)
The idea is too make the form BlankChecks open and show what choice I pick
from the form frmAutoPayrollReport (Combo187) List.
The Combo187 uses as it's record source a table named Customers with the
control customers.
Whereas the form BlankChecks uses as it's record source for the Customer
drop down list the table Employees, but it's control is Customers from the
table
tblchecks which has a customer field in it.
Here is my code I am trying, it does not work, opens the form, but does not
display a record.
The form BlankChecks normally opens to a new record.

Dte1 = Forms!frmAutoPayrollReport!BeginDate
Dte2 = Forms!frmAutoPayrollReport!EndDate
Dim stDocName As String
Dim stLinkCriteria As String
On Error GoTo Err_frmAutoPayrollReport_Click

Forms!frmAutoPayrollReport!Combo187 = Null
stDocName = "BlankChecks"
stLinkCriteria = "[tblchecks]=" & Me![Combo187]

DoCmd.OpenForm "BlankChecks", , , "[Customers] = '" & Me.Combo187 & "'"

Exit_frmAutoPayrollReport_Click:
Exit Sub

Err_frmAutoPayrollReport_Click:
MsgBox Err.Description
Resume Exit_frmAutoPayrollReport_Click
Click to expand...
Click to expand...
Click to expand...
 
Back
Top