GotoRecord determined by several values

[Tep]

Hi,

I am trying to go to a specific record on a form. This is de code I use as event procedure of a button:

DoCmd.GoToRecord acActiveDataObject, , acGoTo, "EU_psu_nr = " & Me.ZEU_psu_nr And "EU_pdg_nr = " & Me.ZEU_pdg_nr And "EU_psg_nr = " & Me.ZEU_psg_nr And "EU_pro_nr = " & Me.ZEU_pro_nr And "psu_nr = " & Me.Zpsu_nr And "pdg_nr = " & Me.Zpdg_nr And "psg_nr = " & Me.Zpsg_nr And "pro_nr = " & Me.Zpro_nr And "pro_nr_hoedanigheid = " & Me.Zpro_nr_hoedanigheid

So, the values of 9 fields (unbound text-boxes: ZEU_psu_nr, ZEU_pdg_nr, etc) determine to which record I would like to go.
When I run this code, I get the message "Run-time error 13. Type mismatch".

I hope somebody can help me with this!

Thanks,
Tep

 

[CBrighton]

Your double-quotes by the And's are in the incorrect places and some ampersand's to link everything together are missing. Also, I assume all the fields are a numeric data type.

DoCmd.GoToRecord acActiveDataObject, , acGoTo, 
"EU_psu_nr = " & Me.ZEU_psu_nr & 
" And EU_pdg_nr = " & Me.ZEU_pdg_nr & 
" And EU_psg_nr = " & Me.ZEU_psg_nr & 
" And EU_pro_nr = " & Me.ZEU_pro_nr & 
" And psu_nr = " & Me.Zpsu_nr & 
" And pdg_nr = " & Me.Zpdg_nr & 
" And psg_nr = " & Me.Zpsg_nr & 
" And pro_nr = " & Me.Zpro_nr & 
" And pro_nr_hoedanigheid = " & Me.Zpro_nr_hoedanigheid

:edit:

line breaks are just for clarity

 

[Tep]

Thanks. I think some quotes or something like that are still missing in your code, while now I get the error "Compile error. Expected: end of statement".
Probably it is very basic, but well... not for me at the moment :-(
You are right that all the fields are numeric. Can you have again have a look at it?
Thanks,
Tep

 

[CBrighton]

First, make sure you have removed the line breaks I added for clarity.

Second, do something like this (add a new command button to the form to do this, it can be deleted after the test):

dim strTest as string
 
strTest = "EU_psu_nr = " & Me.ZEU_psu_nr & " And EU_pdg_nr = " & Me.ZEU_pdg_nr & " And EU_psg_nr = " & Me.ZEU_psg_nr & " And EU_pro_nr = " & Me.ZEU_pro_nr & " And psu_nr = " & Me.Zpsu_nr & " And pdg_nr = " & Me.Zpdg_nr & " And psg_nr = " & Me.Zpsg_nr & " And pro_nr = " & Me.Zpro_nr & " And pro_nr_hoedanigheid = " & Me.Zpro_nr_hoedanigheid
 
debug.print strTest

Run that code then look in the Debug window in the VBA editor.

This will print the resulting string which you are creating so you can see the exact format and spot any mistakes.

 

[Tep]

Ah, ok. That immediate window I did not know. Can be helpfull.
When I run your testcode this is what appears in the debug window:

EU_psu_nr = 1 And EU_pdg_nr = 1 And EU_psg_nr = 0 And EU_pro_nr = 1 And psu_nr = 1 And pdg_nr = 9 And psg_nr = 5 And pro_nr = 16 And pro_nr_hoedanigheid = 1

These values I entered in the unbound fields. So, no errors there.

But when I run the other code (see below), it says now:
"Run-time error 2498. An expression you entered is the wrong data type for one of the arguments".

I checked the data type once more, but it is all numeric. So I do not understand again... I hope you have still some idea!

Thanks,
Tep

DoCmd.GoToRecord acActiveDataObject, , acGoTo, "EU_psu_nr = " & Me.ZEU_psu_nr & " And EU_pdg_nr = " & Me.ZEU_pdg_nr & " And EU_psg_nr = " & Me.ZEU_psg_nr & " And EU_pro_nr = " & Me.ZEU_pro_nr & " And psu_nr = " & Me.Zpsu_nr & " And pdg_nr = " & Me.Zpdg_nr & " And psg_nr = " & Me.Zpsg_nr & " And pro_nr = " & Me.Zpro_nr & " And pro_nr_hoedanigheid = " & Me.Zpro_nr_hoedanigheid

 

[CBrighton]

OK, you have the WHERE clause which is being used (and I agree, it looks fine).

What you want to do now is create a new query, add the tables & fields required, then go into SQL view and add the result of the debug to the WHERE clause.

Basically, see if it's still a mismatch when access processes it in it's own query builder. If it is then it's pretty much certain that at least one of those fields is not numeric.

 

[Tep]

It is a pity, but when I paste the result of the debug into a WHERE clause in a query, just as you suggested, the query runs fine. The correct record is shown in the datasheet view. Strange...
Any more ideas?

 

[CBrighton]

Do you still have the query?

View it in design mode after running it and double check the criteria of each field. I want to make sure Access didn't automatically add quotes around any criteria which is really a text field.

 

[Tep]

After running the query, in the design mode the numbers in the criteria-fields are just stated as numbers. No quotes or something like that are added.

This is the sql:
SELECT dbo_EU_Products_koppeling.PesWebID, dbo_EU_Products_koppeling.EU_psu_nr, dbo_EU_Products_koppeling.EU_pdg_nr, dbo_EU_Products_koppeling.EU_psg_nr, dbo_EU_Products_koppeling.EU_pro_nr, dbo_EU_Products_koppeling.psu_nr, dbo_EU_Products_koppeling.pdg_nr, dbo_EU_Products_koppeling.psg_nr, dbo_EU_Products_koppeling.pro_nr, dbo_EU_Products_koppeling.pro_nr_hoedanigheid
FROM dbo_EU_Products_koppeling
WHERE EU_psu_nr = 1 And EU_pdg_nr = 1 And EU_psg_nr = 0 And EU_pro_nr = 1 And psu_nr = 1 And pdg_nr = 9 And psg_nr = 5 And pro_nr = 16 And pro_nr_hoedanigheid = 1

 

[CBrighton]

Then I'm afraid I don't know.

I see no reason why it shouldn't work if the criteria is perfectly acceptable in a query.

One last idea, put the criteria directly into the VBA:

DoCmd.GoToRecord acActiveDataObject, , acGoTo, "EU_psu_nr = 1 And EU_pdg_nr = 1 And EU_psg_nr = 0 And EU_pro_nr = 1 And psu_nr = 1 And pdg_nr = 9 And psg_nr = 5 And pro_nr = 16 And pro_nr_hoedanigheid = 1"

If it accepts this then it may be the control boxes which you are using for the criteria.

 

[Tep]

The criteria in the VBA gives the same error: "Run-time error 2498. An expression you entered is the wrong data type for one of the arguments".

I hope tomorrow I will have a fresh view on this matter. Anyhow, thank you very much for your support!