Open form based on two combo box selection

[Beatrice]

I need to open a form selecting all data from a previous form i.e:

Form 1)

combo box 1 named "cboYearSelect" displayed as "Year" based on qry QryYearList ( only one field "Year")

combo box 2 named "cbowwselect" displayed as "wk" based on qry Qry WkList (field: Year, wk) displaying only wk field.


OPEN FORM (2) BUTTON (@@@)

Form 2) all my data :

Year
wk

product 1
product 2 and so on

(@@@) in the OnCLick event procedure of this button I inserted the following code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Wkly_data_WIP"

stLinkCriteria = "[Year]=" & Me![cboYearSelect] And "[wk]=" & Me![cbowwselect]
DoCmd.OpenForm stDocName, , , stLinkCriteria

But I obtain the "DataTypeMismatch" error:
where is my mistake?

Thanks a lot in advance
Bea

[puppydogbuddy]

I need to open a form selecting all data from a previous form i.e:

Form 1)

combo box 1 named "cboYearSelect" displayed as "Year" based on qry QryYearList ( only one field "Year")

combo box 2 named "cbowwselect" displayed as "wk" based on qry Qry WkList (field: Year, wk) displaying only wk field.


OPEN FORM (2) BUTTON (@@@)

Form 2) all my data :

Year
wk

product 1
product 2 and so on

(@@@) in the OnCLick event procedure of this button I inserted the following code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Wkly_data_WIP"

stLinkCriteria = "[Year]=" & Me![cboYearSelect] And "[wk]=" & Me![cbowwselect]
DoCmd.OpenForm stDocName, , , stLinkCriteria

But I obtain the "DataTypeMismatch" error:
where is my mistake?

Thanks a lot in advance
Bea

Try this:
stLinkCriteria = "[Year]= " & Me![cboYearSelect] & " And [wk]= " & Me![cbowwselect]

If it does not work, please provide sample of what the output is supposed to look like.

[Beatrice]

Try this:
stLinkCriteria = "[Year]= " & Me![cboYearSelect] & " And [wk]= " & Me![cbowwselect]

If it does not work, please provide sample of what the output is supposed to look like.

Hello PuppydogBuddy,

thansk for your reply, but the error persist also with your suggested modification.

I'll try to explain my problem better:
I have a form where I choose the interested period (Year & Wk):
selected these vallue I want to open a second form with all (filtered)
data related to the period (Year & wk).

If I apply the two condition in the linkcriteria separately they work without data type mismatch...

Thanks,
Bea

[puppydogbuddy]

Hello PuppydogBuddy,

thansk for your reply, but the error persist also with your suggested modification.

I'll try to explain my problem better:
I have a form where I choose the interested period (Year & Wk):
selected these vallue I want to open a second form with all (filtered)
data related to the period (Year & wk).

If I apply the two condition in the linkcriteria separately they work without data type mismatch...

Thanks,
Bea

if year is numeric data type and week is text data type, the syntax would look like as shown below. Additional quotes needed to identify the parameter as a text data type.

Try this:
stLinkCriteria = "[Year]= " & Me![cboYearSelect] & " And [wk]= '" & Me![cbowwselect] & "'"

[Beatrice]

if year is numeric data type and week is text data type, the syntax would look like as shown below. Additional quotes needed to identify the parameter as a text data type.

Try this:
stLinkCriteria = "[Year]= " & Me![cboYearSelect] & " And [wk]= '" & Me![cbowwselect] & "'"

They are both Integer...I'm lost!!!
;-((((

Bea

[puppydogbuddy]

They are both Integer...I'm lost!!!
;-((((

Bea

How are year and week displayed? Example 5 or 05? If 05, it is probably text.

[Beatrice]

How are year and week displayed? Example 5 or 05? If 05, it is probably text.

Wk is displayed as 5 and not as 05...
I've checked in all table/query and the field wk is defined Integer everywhere!

I've changed the Link Criteria in the following way:

stLinkCriteria = "[Year]= " & Val(Me![cboYearSelect]) & " And [wk]= '" & Val(Me![cbowkselect]) & "'"

And now the error message is: "The OpenForm Action was canceled"
I can't understand where is my mistake!

Thanks!
Bea

[puppydogbuddy]

Wk is displayed as 5 and not as 05...
I've checked in all table/query and the field wk is defined Integer everywhere!

I've changed the Link Criteria in the following way:

stLinkCriteria = "[Year]= " & Val(Me![cboYearSelect]) & " And [wk]= '" & Val(Me![cbowkselect]) & "'"

And now the error message is: "The OpenForm Action was canceled"
I can't understand where is my mistake!

Thanks!
Bea

Bea,
the message is cryptic, so I am not sure. Maybe encountering a null?

if it is just a syntax problem, try it this way:
stLinkCriteria = "[Year]= " & Val(Me![cboYearSelect]) & " And [wk]= " & Val(Me![cbowkselect])

[billypit]

Try this one

stLinkCriteria = "[Year]= " & "&Val(Me![cboYearSelect])&" And "[wk]= " & "&Val(Me![cbowkselect]) &"

[sierra7]

Wk is displayed as 5 and not as 05...
I've checked in all table/query and the field wk is defined Integer everywhere!

I've changed the Link Criteria in the following way:

stLinkCriteria = "[Year]= " & Val(Me![cboYearSelect]) & " And [wk]= '" & Val(Me![cbowkselect]) & "'"

And now the error message is: "The OpenForm Action was canceled"
I can't understand where is my mistake!

Thanks!
Bea

A couple of things wrong with the above.
'Year' is a reserved word, it may work but should be avoided
You don't need quotes around the cboWkSelect term which I presume should be an integer

stLinkCriteria = "[Year]= " & Val(Me![cboYearSelect]) & " And [wk]= " & Val(Me![cbowkselect])

If this doesn't work you should consider changing the fieldname 'Year' which I appreciate may have a lot of knock-on consequences.

[Beatrice]

A couple of things wrong with the above.
'Year' is a reserved word, it may work but should be avoided
You don't need quotes around the cboWkSelect term which I presume should be an integer

stLinkCriteria = "[Year]= " & Val(Me![cboYearSelect]) & " And [wk]= " & Val(Me![cbowkselect])

If this doesn't work you should consider changing the fieldname 'Year' which I appreciate may have a lot of knock-on consequences.

Hi all,

thanks so much for all your help...

I've changed "Year" filed name and used the last suggestion successfully!!!

thanks thanks thansk!!!!