Access 2007 Verses 2003 - passing of variables.

[brien1951]

I have a 2003 database that takes some variables from a form and passes them to a couple of queries (The 1st query looks into a table for existing and past problems, the 2nd for the constant information about item in question). With 2003 the variables pass correctly to the queries and I get my results, however with 2007 it ignores my set variables and gives me a popup box for each variable in the query (a total of 6 - 3 for the 1st query and then 3 for the 2nd).

I am trying to find a solution what works for 2007, I had tried (maybe incorrectly) to use a public function to set the variables - that didn't work.

Below are the Variable declarations followed by the part of the subrountine take runs the queries. As you can see, I clear by listboxs and then set my variables to the value of the combo boxes from the form.

Option Compare Database
Public TSPvar
Public ADDRvar
Public Machinevar
Option Explicit
______________________________________________________________
Private Sub TSPAddrlookcmd_Click()
PDQlistbox4.RowSource = ""
CRMlistbox4.RowSource = ""
Machinevar = Systemcombo.Value
TSPvar = TSPcombo.Value
ADDRvar = Addrcombo.Value
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DSO_Table.ID,DSO_Table.CRMtkt,DSO_Table.MACHINE,DSO_Table.NAP_NIU,DSO_Table.NAP_PORT,DSO_Table.CO,DSO_Table.Pilot,DSO_Table.POTS,DSO_Table.Ter,DSO_Table.Description,DSO_Table.Opndte,DSO_Table.Opnuuid,DSO_Table.Clsdte,DSO_Table.Clsuuid,DSO_Table.Clssolution into tempcrmtb FROM DSO_Table WHERE (((DSO_Table.MACHINE)=Machinevar) and ((DSO_Table.NAP_NIU)=TSPvar)) and ((DSO_Table.NAP_PORT)=ADDRvar)ORDER BY DSO_Table.Opndte Desc;"
DoCmd.SetWarnings True
CRMlistbox4.ColumnWidths = "0;1600;0;0;0;0;0;0;0;2400;1400;0;1400;0;2980"
CRMlistbox4.RowSource = "tempcrmtb"
CRMlistbox4.Requery
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT PORT_TABLE.MACHINE,PORT_TABLE.CO,PORT_TABLE.MD,PORT_TABLE.ACCESS, PORT_TABLE.POTS,PORT_TABLE.TER,PORT_TABLE.NAP_NIU,PORT_TABLE.NAP_PORT,PORT_TABLE.PRIM,PORT_TABLE.T1_PORT into tempdso FROM PORT_TABLE WHERE (((PORT_TABLE.MACHINE)=Machinevar)and ((PORT_TABLE.NAP_NIU)=TSPvar) and ((PORT_TABLE.NAP_PORT)=ADDRvar)) ORDER BY PORT_TABLE.POTS Desc;"
DoCmd.SetWarnings True

 

[brien1951]

A simpler example

A simpler example is below 2003 valid code, followed by 2007 code that I found under Access help:

2003 code:

WhoRU = Logincombo NOTE: logincombo is a combo box on the MAIN form

DoCmd.RunSQL "SELECT prblmtable.uuid into tempuuid from prblmtable WHERE (((prblmtable.uuid)= WhoRU));"

Running the above code results in a text box called "WhoRU", so I tried changing the code to what is shown below but all that did was change the name of the text box to whatever value had been entered into the combo box on the form:

DoCmd.RunSQL "SELECT prblmtable.uuid into tempuuid from prblmtable WHERE (((prblmtable.uuid)= " & WhoRU & "));"



2007 code: NOTE: I didn't do the "into" part of the query as I was trying to debug the code.

Dim dbs As Database, quuid As QueryDef, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT prblmtable.uuid FROM prblmtable " _

& "WHERE (((prblmtable.uuid)= " & Forms!Main!Logincombo & "));"

Set quuid = dbs.CreateQueryDef(strSQL)




This is the result:

Run-time error '3125':

'SELECT prblmtable.uuid FROM prblmtable WHERE (((prblmtable.uuid)=bd5168));' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

NOTE: "bd5168" was the value that was entered in the Logincombo on the "Main" form.

 

[brien1951]

Solved:


I found that instead of using a variable I can use the value directly from the form.

Instead of : WHERE (((prblmtable.uuid)= WhoRU));"
I use this instead: WHERE (((prblmtable.uuid)= Forms!Main!Logincombo));"

for other "constant" variables, I have to enter the value in double quotes as part of the query:

WHERE (((DSO_Fax_Table.O_R)= crmvar and(DSO_Fax_Table.Date)=date()));
becomes
WHERE ((Not(DSO_Fax_Table.O_R)= ""T"" and(DSO_Fax_Table.Date)=date()));