Command button code needed to open specific form

G

Guest

Below is the current command button code which basically opens all forms
within frmContractRates--Specifications
This command button is clicked after the user chooses a specific Contract
Name and Contract Rate Sheet Name from cmbContractName drop down and
cmbContractRateSheetName drop down respectfully.
How do I code the event procedure so that when the specific Contract Name
and Contract Rate Sheet Name are chosen only the specified form opens in the
frmContractRates--Specifications form?
Thank you for your help.
Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub
 
D

Duane Hookom

You can update the stLinkCriteria like:

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String
'set the criteria assuming both fields are text type
stLinkCriteria = "[Contract Name] =""" & cmbContractName _
& """ AND [Contract Rate Sheet Name] =""" _
& cmbContractRateSheetName & """"
stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub
 
G

Guest

This is what I put in, & the error message I got was the following:
Compile Error:
Expected: line number or label statement or end of statement

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""


stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

Please help, I may have not put in enough spaces, etc.

Duane Hookom said:
You can update the stLinkCriteria like:

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String
'set the criteria assuming both fields are text type
stLinkCriteria = "[Contract Name] =""" & cmbContractName _
& """ AND [Contract Rate Sheet Name] =""" _
& cmbContractRateSheetName & """"
stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

--
Duane Hookom
MS Access MVP
--

Mary A Perez said:
Below is the current command button code which basically opens all forms
within frmContractRates--Specifications
This command button is clicked after the user chooses a specific Contract
Name and Contract Rate Sheet Name from cmbContractName drop down and
cmbContractRateSheetName drop down respectfully.
How do I code the event procedure so that when the specific Contract Name
and Contract Rate Sheet Name are chosen only the specified form opens in
the
frmContractRates--Specifications form?
Thank you for your help.
Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub
Click to expand...
Click to expand...
 
D

Duane Hookom

This isn't how I suggested:
stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""

You must have a space to the left of the underscore. You also need to finish
with four double quotes
stLinkCriteria = "[ContractName]=""" & cmbContractName _
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &""""

--
Duane Hookom
MS Access MVP
--

Mary A Perez said:
This is what I put in, & the error message I got was the following:
Compile Error:
Expected: line number or label statement or end of statement

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""


stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

Please help, I may have not put in enough spaces, etc.

Duane Hookom said:
You can update the stLinkCriteria like:

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String
'set the criteria assuming both fields are text type
stLinkCriteria = "[Contract Name] =""" & cmbContractName _
& """ AND [Contract Rate Sheet Name] =""" _
& cmbContractRateSheetName & """"
stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

--
Duane Hookom
MS Access MVP
--

Mary A Perez said:
Below is the current command button code which basically opens all
forms
within frmContractRates--Specifications
This command button is clicked after the user chooses a specific
Contract
Name and Contract Rate Sheet Name from cmbContractName drop down and
cmbContractRateSheetName drop down respectfully.
How do I code the event procedure so that when the specific Contract
Name
and Contract Rate Sheet Name are chosen only the specified form opens
in
the
frmContractRates--Specifications form?
Thank you for your help.
Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok, the code works, except it's not pulling the specific form. It's
filtering to a blank one.
Any suggestions?
I have the ContractName and the ContractRateSheetName and their respective
Id's in the table for the form, so I'm not sure why it's not connecting,
unless I need a query as well.
Thank you for your patience.

Duane Hookom said:
This isn't how I suggested:
stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""

You must have a space to the left of the underscore. You also need to finish
with four double quotes
stLinkCriteria = "[ContractName]=""" & cmbContractName _
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &""""

--
Duane Hookom
MS Access MVP
--

Mary A Perez said:
This is what I put in, & the error message I got was the following:
Compile Error:
Expected: line number or label statement or end of statement

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""


stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

Please help, I may have not put in enough spaces, etc.

Duane Hookom said:
You can update the stLinkCriteria like:

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String
'set the criteria assuming both fields are text type
stLinkCriteria = "[Contract Name] =""" & cmbContractName _
& """ AND [Contract Rate Sheet Name] =""" _
& cmbContractRateSheetName & """"
stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

--
Duane Hookom
MS Access MVP
--

Below is the current command button code which basically opens all
forms
within frmContractRates--Specifications
This command button is clicked after the user chooses a specific
Contract
Name and Contract Rate Sheet Name from cmbContractName drop down and
cmbContractRateSheetName drop down respectfully.
How do I code the event procedure so that when the specific Contract
Name
and Contract Rate Sheet Name are chosen only the specified form opens
in
the
frmContractRates--Specifications form?
Thank you for your help.
Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

"pulling the specific form"? You need to understand that forms don't contain
records, they display records. I assume you mean the stLinkCriteria isn't
working.

You should add code

stLinkCriteria = "[ContractName]=""" & cmbContractName _
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &""""
Debug.Print stLinkCriteria

You can then open the debug/immediate window (press ctrl+G) and copy the
stLinkCriteria to your clipboard.

Then open the record source query of your form and go to the sql view. Paste
the stLinkCriteria value into the appropriate place in the SQL to see where
your error is.
--
Duane Hookom
MS Access MVP
--

Mary A Perez said:
Ok, the code works, except it's not pulling the specific form. It's
filtering to a blank one.
Any suggestions?
I have the ContractName and the ContractRateSheetName and their respective
Id's in the table for the form, so I'm not sure why it's not connecting,
unless I need a query as well.
Thank you for your patience.

Duane Hookom said:
This isn't how I suggested:
stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""

You must have a space to the left of the underscore. You also need to
finish
with four double quotes
stLinkCriteria = "[ContractName]=""" & cmbContractName _
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &""""

--
Duane Hookom
MS Access MVP
--

Mary A Perez said:
This is what I put in, & the error message I got was the following:
Compile Error:
Expected: line number or label statement or end of statement

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ContractName]=""" & cmbContractName_
& """ AND [ContractRateSheetName]=""" _
& cmbContractRateSheetName &"""


stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

Please help, I may have not put in enough spaces, etc.

:

You can update the stLinkCriteria like:

Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String
'set the criteria assuming both fields are text type
stLinkCriteria = "[Contract Name] =""" & cmbContractName _
& """ AND [Contract Rate Sheet Name] =""" _
& cmbContractRateSheetName & """"
stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub

--
Duane Hookom
MS Access MVP
--

Below is the current command button code which basically opens all
forms
within frmContractRates--Specifications
This command button is clicked after the user chooses a specific
Contract
Name and Contract Rate Sheet Name from cmbContractName drop down and
cmbContractRateSheetName drop down respectfully.
How do I code the event procedure so that when the specific Contract
Name
and Contract Rate Sheet Name are chosen only the specified form
opens
in
the
frmContractRates--Specifications form?
Thank you for your help.
Private Sub cmdClickforContractInformation1_Click()
On Error GoTo Err_cmdClickforContractInformation1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation1_Cli:
Exit Sub

Err_cmdClickforContractInformation1_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation1_Cli

End Sub
Click to expand...
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

Record won't open when selected by user 11
Need to add code to code filtering form... 2
Code won't work with additions... 10
Issure wiht subforms and command button 9
Command Button linked to SubForm 5
Open form with Command button Error 3
can't find form 4
command button to open form needs to assign values 1

Top