Help with SQL Statement

[AlanAnderson]

Hi Guys,

I need to look through a table (tblVAT). If I find a record where field VATMonth is equal to my variable (varMonth) then I need to edit the other fields in that record.

If I reach EOF then I need to add a new record to the table.

The SQL I've used (Which doesn't do half the job - think of it as pseudocode ) is:

Set rst=Currentdb.OpenRecordset(name:="SELECT * FROM tblVAT WHERE VATMONTH=varMonth", Type:=dbOPenDynaset

Any ideas would really be appreciated.

Regards,

Alan

 

[MSAccessRookie]

Hi Guys,

I need to look through a table (tblVAT). If I find a record where field VATMonth is equal to my variable (varMonth) then I need to edit the other fields in that record.

If I reach EOF then I need to add a new record to the table.

The SQL I've used (Which doesn't do half the job - think of it as pseudocode ) is:

Set rst=Currentdb.OpenRecordset(name:="SELECT * FROM tblVAT WHERE VATMONTH=varMonth", Type:=dbOPenDynaset

Any ideas would really be appreciated.

Regards,

Alan

If I understand your situation correctly, then your SQL is improperly formatted. The VBA Variable is unknown the the SQL Processor, and needs to be interpreted instead. To do this, you will need to add it to the end of the SQL Statement as a string. See if the updated example below works any better.

Set rst=Currentdb.OpenRecordset(name:="SELECT * FROM tblVAT WHERE VATMONTH='" & varMonth & "'", Type:=dbOPenDynaset

 

[AlanAnderson]

Hi,
Thank you for that - I will give it a try.

But that's only half the problem. What will that statement do if no record is found?
In that case, I need to add that record not just edit the existing (found) record.

Regards,

Alan

 

[maximark]

After the suggested statement

Set rst=Currentdb.OpenRecordset(name:="SELECT * FROM tblVAT WHERE VATMONTH='" & varMonth & "'", Type:=dbOPenDynaset

wouldn't something like this work

If rst.BOF and rst.EOF then
'add your new record
else
'edit the other fields
end if


.

 

[AlanAnderson]

Hi Guys,

That statement doesn't appear to work. If I place a "watch" on VATMonth it seems that Access can't find it. It says "Expression not defined in context" yet that exact field name does exist in the table. ?????

Regards,

Alan

 

[maximark]

then you have to prefice VATMonth with the table name otherwise it isn't defined.

also maybe to check if the select statement is working remove the whole WHERE clause and step(f8) through the code to see if the recordset is created and has records.