DAO problem

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I call the following function, which looks up the numeric
value iRecord, which I want to use as the criteria to look
up the information from the second query. This works fine
and prints the appropriate value in the immediate window
following the debug.print line, the problem occurs when
iRecord is used an error message "The Microsoft Jet
database does not recognise 'iRecord' as a valid field
name or expression". Should the iRecord value be converted
to a text value along with the value in the field payref.
If so some help with the syntax would be appreciated.

Dim dbnewpayments As DAO.Database
Dim rcdWOlines As DAO.Recordset
Set dbnewpayments = CurrentDb
Set rcdWOlines =
_dbnewpayments.OpenRecordset "qryWOlookup")

Dim iRecord As Variant
Dim iSCompany As Variant

rcdWOlines.FindFirst "ALTCODE = 'WO' And [WOtest] = no"
If Not rcdWOlines.NoMatch Then
iRecord = rcdWOlines![record]
Debug.Print iRecord
Dim dbINVpayments As DAO.Database
Dim rcdINVlines As DAO.Recordset
Set dbINVpayments = CurrentDb
Set rcdINVlines = dbnewpayments.OpenRecordset_
("qryinvlookup")

rcdINVlines.FindFirst "payref = iRecord"
 
Hello Joe,

I think your problem lies in the following line:

rcdINVlines.FindFirst "payref = iRecord"

Change it to

rcdINVlines.FindFirst "payref = " & iRecord

The way you had wrote it was like saying if payref = iRecord literally. The
new way, the value of iRecord is added to the end of the string instead of
searching for records that match the string "iRecord". For your information,
if the data type of the field payref was a string, you would need to inclue
' in the string and if the datatype was a date you would use the # symbol.
The error you have described is a classic case of someone missing the ' out
of the search criteria. Access then interprites the string incorectly and
then displays an error message. In your case, you are trying to search for a
string ("iRecord") when it is expecting a number. Examples of searching for
the additional data types I have just mentioned are:

Strings:
rcdINVlines.FindFirst "payref = '" & iRecord & "'"

Dates:
rcdINVlines.FindFirst "payref = #" & iRecord & "#"

HTH,

Neil.
 
Hello Niel

Thanks for your tips, the alterations worked and I was
able to find the appropriate record. I would appreciate it
If you could indulge me once again.

The next stage in the code should then use the references
collected to enable the first record to be updated I have
hit another problem. When the irecord and iscompany
variables have been set the idea is to update the original
record with the iscompany data obtained from the second
part of the procedure, the SCOMPANY field is numeric and
set to double. No matter how I do this the record won't
update.

Second code section

Dim dbINVpayments As DAO.Database
Dim rcdINVlines As DAO.Recordset
Set dbINVpayments = CurrentDb
Set rcdINVlines = dbnewpayments.OpenRecordset
("qryinvlookup")
'use the payref to find the record
rcdINVlines.FindFirst "payref = " & iRecord
'declare the iscompany as a variant
Dim iSCompany As Variant
'obtain the company ref from the current records
scompany field
iSCompany = rcdINVlines![SCOMPANY]
'test to see if the variable has been set
MsgBox "scompany" & " " & iSCompany
' all appears fine at this point the correct company
has been picked up

Dim dbupdate As DAO.Database
Dim rcdcompany As DAO.Recordset
Set dbupdate = CurrentDb
Set rcdcompany = dbnewpayments.OpenRecordset
("qrycompanyupdate")
'move to the appropriate record
rcdcompany.FindFirst "record_num = " & iRecord
rcdcompany.Edit
'update the field wotest to "yes"
rcdcompany![WOTest] = rcdcompany![WOTest] = yes
'update the field scompany to the value held in
iscompany
rcdcompany![SCOMPANY] = rcdcompany![SCOMPANY] =
iSCompany
rcdcompany.Update
'the wotest field is updated correctly but the
scompany does not alter their are no error messages
 
Joe said:
Hello Niel

Thanks for your tips, the alterations worked and I was
able to find the appropriate record. I would appreciate it
If you could indulge me once again.

The next stage in the code should then use the references
collected to enable the first record to be updated I have
hit another problem. When the irecord and iscompany
variables have been set the idea is to update the original
record with the iscompany data obtained from the second
part of the procedure, the SCOMPANY field is numeric and
set to double. No matter how I do this the record won't
update.

Second code section

Dim dbINVpayments As DAO.Database
Dim rcdINVlines As DAO.Recordset
Set dbINVpayments = CurrentDb
Set rcdINVlines = dbnewpayments.OpenRecordset
("qryinvlookup")
'use the payref to find the record
rcdINVlines.FindFirst "payref = " & iRecord
'declare the iscompany as a variant
Dim iSCompany As Variant
'obtain the company ref from the current records
scompany field
iSCompany = rcdINVlines![SCOMPANY]
'test to see if the variable has been set
MsgBox "scompany" & " " & iSCompany
' all appears fine at this point the correct company
has been picked up

Dim dbupdate As DAO.Database
Dim rcdcompany As DAO.Recordset
Set dbupdate = CurrentDb
Set rcdcompany = dbnewpayments.OpenRecordset
("qrycompanyupdate")
'move to the appropriate record
rcdcompany.FindFirst "record_num = " & iRecord
rcdcompany.Edit
'update the field wotest to "yes"
rcdcompany![WOTest] = rcdcompany![WOTest] = yes
'update the field scompany to the value held in
iscompany
rcdcompany![SCOMPANY] = rcdcompany![SCOMPANY] =
iSCompany
rcdcompany.Update
'the wotest field is updated correctly but the
scompany does not alter their are no error messages
Click to expand...

Dim dbupdate As DAO.Database
Dim rcdcompany As DAO.Recordset
Set dbupdate = CurrentDb
* Set rcdcompany = dbupdate.OpenRecordset ("qrycompanyupdate")
rcdcompany.FindFirst "record_num = " & iRecord

* if rcdcompany.Nomatch then
* 'display error message
* else
rcdcompany.Edit
rcdcompany![WOTest] = yes
rcdcompany![SCOMPANY] = iSCompany
rcdcompany.Update
* endif


Your logic can't tell if it found the corre4ct record or not.
Also you don't need to do this:

rcdcompany![WOTest] = rcdcompany![WOTest] = yes

Just this:
rcdcompany![WOTest] = yes

I don't see were you defined or used "dbnewpayments".


Ron
 
Hello Ronald

Many thanks for your suggestions. This has cured the
problem apart from your last line where you stated that

rcdcompany![WOTest] = rcdcompany![WOTest] = yes

was not necessary. I have not been able to make the
function work using your shortened code. But it does work
using your code along with the full line as above.

Being fairly new to VBA the "community news group" is an
in valuable source, and I would like to thank you for your
contribution to my enquiry.

Joe
 
Joe said:
Hello Ronald

Many thanks for your suggestions. This has cured the
problem apart from your last line where you stated that

rcdcompany![WOTest] = rcdcompany![WOTest] = yes

was not necessary. I have not been able to make the
function work using your shortened code. But it does work
using your code along with the full line as above.

Being fairly new to VBA the "community news group" is an
in valuable source, and I would like to thank you for your
contribution to my enquiry.

Joe
Click to expand...
Open the query "qrycompanyupdate" and see if you can manually change any of
the data. I don't understand why it only works the way you have it coded.
Maybe you should us True or False vs Yes or No.

Ron
 
Back
Top