By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,839 Members | 2,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,839 IT Pros & Developers. It's quick & easy.

Open form based on two combo box selection

P: 15
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
Nov 30 '07 #1
Share this Question
Share on Google+
10 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Nov 30 '07 #2

P: 15
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
Nov 30 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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] & "'"
Nov 30 '07 #4

P: 15
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
Nov 30 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
They are both Integer...I'm lost!!!
;-((((

Bea
How are year and week displayed? Example 5 or 05? If 05, it is probably text.
Nov 30 '07 #6

P: 15
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
Dec 3 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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])
Dec 3 '07 #8

P: 5
Try this one

stLinkCriteria = "[Year]= " & "&Val(Me![cboYearSelect])&" And "[wk]= " & "&Val(Me![cbowkselect]) &"
Dec 3 '07 #9

Expert 100+
P: 446
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.
Dec 3 '07 #10

P: 15
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!!!!
Dec 4 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.