Link Criteria again

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2000

I have a form I call "Pop-up Update Allocations" (form 1). This form has an
unbound combo box (cboCostCenter1) that is used to filter the selected
records that open in the next form called "Expenditure Allocation Update"
(form 2). This type of filter works in another application but the factors
must be somewhat different in this application because I get a window
prompted by the form 2's query that says Enter Parameter "Pop-up Update
Allocations1.cboCostCenter1" prompting the input that was input in form 1. If
I do not input any value in the window and click ok, the filter ultimately
works. Maybe a timing issue?


The following is the code I used to copy the entry on form 1 in field
cboCostCenter1 to form 2 field CC:

Private Sub Form_Open(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Pop-up Update Allocations"
stLinkCriteria = "[Pop-up Update Allocations].[cboCostCenter1]=" & "'" &
Me![CC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

The above code is in the On Open Event

As I said the Criteria links and the form's query reports the correct
records but I get the query's window asking for the input it alread has. How
can I rewrite the code or change the location of the event to correct this
situation?
 
Access 2000

I have a form I call "Pop-up Update Allocations" (form 1). This form has an
unbound combo box (cboCostCenter1) that is used to filter the selected
records that open in the next form called "Expenditure Allocation Update"
(form 2). This type of filter works in another application but the factors
must be somewhat different in this application because I get a window
prompted by the form 2's query that says Enter Parameter "Pop-up Update
Allocations1.cboCostCenter1" prompting the input that was input in form 1. If
I do not input any value in the window and click ok, the filter ultimately
works. Maybe a timing issue?

The following is the code I used to copy the entry on form 1 in field
cboCostCenter1 to form 2 field CC:

Private Sub Form_Open(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Pop-up Update Allocations"
stLinkCriteria = "[Pop-up Update Allocations].[cboCostCenter1]=" & "'" &
Me![CC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

The above code is in the On Open Event

As I said the Criteria links and the form's query reports the correct
records but I get the query's window asking for the input it alread has. How
can I rewrite the code or change the location of the event to correct this
situation?
Click to expand...

Aren't you trying to open the wrong form?
At least that's what I get from your message.
And certainly your Where clause is not correct.
Form's don't actually contain data. They display the data stored in
tables (or queries), so your criteria must be that the field in the
table (that is the recordsorce for Form2) equals the data shown on the
bound column of the Form1 combo box.
It may be that the name of that table field is "CC" and the name of
the control on the Form2 is also "CC", but that is not necessarily so.

Private Sub Form_Open(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Expenditure Allocation Update" ' This is Form2

stLinkCriteria = "[A Field in the 2nd Form's recordset] = '" &
Me![cboCostCenter1] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Note: Your message is unclear what the actual name of the field in
the table (used by Form2) is, nor it's datatype. I've assumed it is a
Text datatype.

Substitute the actual field names in the above code.

If the stored data is actually a Number datatype then use:
stLinkCriteria = "[A Field in the 2nd Form's recordset] = " &
Me![cboCostCenter1]

I hope this has helped.
 
Fred,

Sorry I neglected to say that the field on form 1 is an unbound field with a
number data type query for just the combo box. This form is what I call a
"Pop-up form" used to select the cost center number in form 1, field
cboCostCenter1 to be used as the filter for the CC field in the query behind
form 2. In the CC (cost center) field criteria of the query I have the
following statement:

[Forms]![Pop-up Update Revenue1]![cboCostCenter1]

From what I have been told, the query looks to form 1, cboCostCenter1 which
is an unbound field and places the value of the unbound combo box into the
query criteria to limit the records to only that cost center for form 2.

I am not an expert on any of this so I am confused about using a "Where
Clause". The following is what I sent:

Sub Form_Open(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Pop-up Update Allocations"
stLinkCriteria = "[Pop-up Update Allocations].[cboCostCenter1]=" & "'" &
Me![CC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
Click to expand...

Does something above represent a Where Clause?

I changed the format of my code from text to number and that works but I
still get the >Enter Parameter "Pop-up Update Allocations1.cboCostCenter1"
prompting the input that was input in form 1< window. How can I get the form
1, cboCostCenter1 data to form 2, field CC faster before the Enter Parameter
window is called by the query?


--
Thanks,

Dennis


fredg said:
Access 2000

I have a form I call "Pop-up Update Allocations" (form 1). This form has an
unbound combo box (cboCostCenter1) that is used to filter the selected
records that open in the next form called "Expenditure Allocation Update"
(form 2). This type of filter works in another application but the factors
must be somewhat different in this application because I get a window
prompted by the form 2's query that says Enter Parameter "Pop-up Update
Allocations1.cboCostCenter1" prompting the input that was input in form 1. If
I do not input any value in the window and click ok, the filter ultimately
works. Maybe a timing issue?

The following is the code I used to copy the entry on form 1 in field
cboCostCenter1 to form 2 field CC:

Private Sub Form_Open(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Pop-up Update Allocations"
stLinkCriteria = "[Pop-up Update Allocations].[cboCostCenter1]=" & "'" &
Me![CC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

The above code is in the On Open Event

As I said the Criteria links and the form's query reports the correct
records but I get the query's window asking for the input it alread has. How
can I rewrite the code or change the location of the event to correct this
situation?
Click to expand...

Aren't you trying to open the wrong form?
At least that's what I get from your message.
And certainly your Where clause is not correct.
Form's don't actually contain data. They display the data stored in
tables (or queries), so your criteria must be that the field in the
table (that is the recordsorce for Form2) equals the data shown on the
bound column of the Form1 combo box.
It may be that the name of that table field is "CC" and the name of
the control on the Form2 is also "CC", but that is not necessarily so.

Private Sub Form_Open(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Expenditure Allocation Update" ' This is Form2

stLinkCriteria = "[A Field in the 2nd Form's recordset] = '" &
Me![cboCostCenter1] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Note: Your message is unclear what the actual name of the field in
the table (used by Form2) is, nor it's datatype. I've assumed it is a
Text datatype.

Substitute the actual field names in the above code.

If the stored data is actually a Number datatype then use:
stLinkCriteria = "[A Field in the 2nd Form's recordset] = " &
Me![cboCostCenter1]

I hope this has helped.
Click to expand...
 
Back
Top