Vba Dlookup

[shamas21]

Hi All

I have a problem with the DLOOKUP in VBA

rst!Name = DLookup("[Name]", "[Asset Register]", "RIGHT([Asset Number],4)= RIGHT(Tables!Table1![Computer],4)")

It produces this error
"You canceled the previous operation."

rst!Name is my recordset.

Please Help

 

[Rabbie]

Hi All

I have a problem with the DLOOKUP in VBA

rst!Name = DLookup("[Name]", "[Asset Register]", "RIGHT([Asset Number],4)= RIGHT(Tables!Table1![Computer],4)")

It produces this error
"You canceled the previous operation."

rst!Name is my recordset.

Please Help

I am a little confused by what you say. What do you mean by "
rst!Name is my recordset."?

Rst is the recordset. rst!Name is a field in your record. Your code is trying to change the value of the Name field in your current record. You would need to use a command such as rst.edit or rst.addnew before changing this value.

It would be helpful if you could post more of the surrounding code so we can help you

 

[gemma-the-husky]

mind you, your where clause looks a bit suspect

"RIGHT([Asset Number],4)= RIGHT(Tables!Table1![Computer],4)"

not sure what you are trying to do, but that just can't work.

 

[shamas21]

I am a little confused by what you say. What do you mean by "
rst!Name is my recordset."?

Rst is the recordset. rst!Name is a field in your record. Your code is trying to change the value of the Name field in your current record. You would need to use a command such as rst.edit or rst.addnew before changing this value.

It would be helpful if you could post more of the surrounding code so we can help you

Hi

Heres the code

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table1")
 
rst.AddNew
rst!Date = Now()
rst!Name = DLookup("computer", "table2", "name = 'shamal'")
rst.Update

 

[Rabbie]

I see you have a field called Date in Table 1. Not a good idea. Date is a reserved word in Access and this can cause problems.

Also check your DLOOKUP

 

[boblarson]

And having a field named Name is another reserved word which can cause problems.

See here for a list of reserved words to not use as field or object names:

http://www.allenbrowne.com/AppIssueBadWord.html

 

[ajetrumpet]

Like Bob said I think...

Hi

Heres the code

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table1")
 
rst.AddNew
rst!Date = Now() [COLOR="Red"]<---a field named DATE????  Not good.[/COLOR]
rst!Name = DLookup("computer", "table2", "name = 'shamal'") [COLOR="Red"][COLOR="Red"]<--- Also a field 
                                               named NAME.  Change this!  :)[/COLOR][/COLOR]
rst.Update

I don't see anything else wrong other than those potential conflicts. It's possible that not surrounding the field names (in both the 1st and 3rd parts of the function) in brackets [] can cause the error, but i seriously doubt it.