Event Procedure

[Guest]

Hi All,
I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.
TIA,
Cheryl

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenTransactions_Click:
Exit Sub

Err_cmdOpenTransactions_Click:
MsgBox Err.Description
Resume Exit_cmdOpenTransactions_Click

End Sub

 

[Marshall Barton]

I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Insert the line:

stLinkCriteria = "[bank ID] = " & Me.[bank ID]

 

[Guest]

This did not work. The same form opens up with all the records. Does it
matter that the record source for the form is a query?

I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Insert the line:

stLinkCriteria = "[bank ID] = " & Me.[bank ID]

 

[Marshall Barton]

Doesn't matter. Queries are treated pretty much the same as
tables nearly everywhere in Access.

Please provide more details about the field names and values
involved.
--
Marsh
MVP [MS Access]

This did not work. The same form opens up with all the records. Does it
matter that the record source for the form is a query?

I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Insert the line:

stLinkCriteria = "[bank ID] = " & Me.[bank ID]

 

[Guest]

I am attempting to write a bank reconciliation application where the user
starts at a main switchboard and enters the bank ID no. By entering specific
data & clicking OK, the next form is retrieved. So far 2 forms have
correctly opened. This last form has all transactions from all banks and
therefore need to be filtererd to the specific bank. This form is opens up
in datasheet view

I checked the code on the previous forms (which follow the code you gave)
and these work fine. The bank ID (Bank_Account) is the primary key for all
forms. The last form (transaction) is a pass thru query that is a subform
(it is one of 3 in a tab format). In looking at the properties, I see that
the child & master link is empty. Could this be the problem? Here is the
revised code:

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"

stLinkCriteria = "[Bank_Account]=" & "'" & Me![Bank_Account] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenTransactions_Click:
Exit Sub

Err_cmdOpenTransactions_Click:
MsgBox Err.Description
Resume Exit_cmdOpenTransactions_Click

End Sub

Doesn't matter. Queries are treated pretty much the same as
tables nearly everywhere in Access.

Please provide more details about the field names and values
involved.
--
Marsh
MVP [MS Access]

This did not work. The same form opens up with all the records. Does it
matter that the record source for the form is a query?

Cheryl wrote:
I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Insert the line:

stLinkCriteria = "[bank ID] = " & Me.[bank ID]

 

[Marshall Barton]

Since you do not "open" a subform, another method of
filtering its records needs to be used. If you want to
filter the subform to the main form record, then the Link
Master/Child properties are ideal. set the link master to
the main form record's linking (primary key?) field and the
link child to the subform's corresponding foreign key field.
--
Marsh
MVP [MS Access]

I am attempting to write a bank reconciliation application where the user
starts at a main switchboard and enters the bank ID no. By entering specific
data & clicking OK, the next form is retrieved. So far 2 forms have
correctly opened. This last form has all transactions from all banks and
therefore need to be filtererd to the specific bank. This form is opens up
in datasheet view

I checked the code on the previous forms (which follow the code you gave)
and these work fine. The bank ID (Bank_Account) is the primary key for all
forms. The last form (transaction) is a pass thru query that is a subform
(it is one of 3 in a tab format). In looking at the properties, I see that
the child & master link is empty. Could this be the problem? Here is the
revised code:

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"

stLinkCriteria = "[Bank_Account]=" & "'" & Me![Bank_Account] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenTransactions_Click:
Exit Sub

Err_cmdOpenTransactions_Click:
MsgBox Err.Description
Resume Exit_cmdOpenTransactions_Click

End Sub

Doesn't matter. Queries are treated pretty much the same as
tables nearly everywhere in Access.

Please provide more details about the field names and values
involved.

This did not work. The same form opens up with all the records. Does it
matter that the record source for the form is a query?

:

Cheryl wrote:
I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Insert the line:

stLinkCriteria = "[bank ID] = " & Me.[bank ID]

 

[Guest]

When I liknk the master/child to Bank_Account I get an error message saying
"You can't use a pass thru query or a non-fixed column crosstab query as a
record source for a subform or subreport". I am exporting one table from my
database to Access. Is there another method to get the data into Access that
will allow me to link the main form to the subform?

Since you do not "open" a subform, another method of
filtering its records needs to be used. If you want to
filter the subform to the main form record, then the Link
Master/Child properties are ideal. set the link master to
the main form record's linking (primary key?) field and the
link child to the subform's corresponding foreign key field.
--
Marsh
MVP [MS Access]

I am attempting to write a bank reconciliation application where the user
starts at a main switchboard and enters the bank ID no. By entering specific
data & clicking OK, the next form is retrieved. So far 2 forms have
correctly opened. This last form has all transactions from all banks and
therefore need to be filtererd to the specific bank. This form is opens up
in datasheet view

I checked the code on the previous forms (which follow the code you gave)
and these work fine. The bank ID (Bank_Account) is the primary key for all
forms. The last form (transaction) is a pass thru query that is a subform
(it is one of 3 in a tab format). In looking at the properties, I see that
the child & master link is empty. Could this be the problem? Here is the
revised code:

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"

stLinkCriteria = "[Bank_Account]=" & "'" & Me![Bank_Account] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenTransactions_Click:
Exit Sub

Err_cmdOpenTransactions_Click:
MsgBox Err.Description
Resume Exit_cmdOpenTransactions_Click

End Sub

Doesn't matter. Queries are treated pretty much the same as
tables nearly everywhere in Access.

Please provide more details about the field names and values
involved.


Cheryl wrote:

This did not work. The same form opens up with all the records. Does it
matter that the record source for the form is a query?

:

Cheryl wrote:
I have a form that will prompt the user to select "OK" to retrieve records.
I would like to filter the records that are retrieved so that the associated
records show up. When the user is in bank ID #103 and clicks on OK, the new
form that pops up should only have data pertaining to bank ID #103. It
currently has records for all banks.

Private Sub cmdOpenTransactions_Click()
On Error GoTo Err_cmdOpenTransactions_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transactions"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Insert the line:

stLinkCriteria = "[bank ID] = " & Me.[bank ID]

 

[Marshall Barton]

When I liknk the master/child to Bank_Account I get an error message saying
"You can't use a pass thru query or a non-fixed column crosstab query as a
record source for a subform or subreport". I am exporting one table from my
database to Access. Is there another method to get the data into Access that
will allow me to link the main form to the subform?

Sorry Cheryl, but I have no idea. I have never needed to
use a pass thru query in a form. Try clarifying this aspect
of your problem and posting a new question (in a forms
group).