Dlookup

[shamas21]

Hi All

I cant get this DLOOPUP to Work. I have tried a lot, even posting threads before this.

It comes up with a message ‘Run Time error 2001: You cancelled the repvious operation’

Here is a quick glimpse at the DLookup,

[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]DLookup("[Name]", "[Asset Register]", "RIGHT([Asset Number],4)=" & r)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

Below is where the DLookup is incorporated.

[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Private Sub Form_AfterUpdate()[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Dim cn As New ADODB.Connection[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim rs As New ADODB.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim i, j As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim r As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    cn.Provider = "Microsoft.Jet.OLEDB.4.0"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    cn.Open "G:\Knowledge\EndUser.mdb"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    'Output the list of all users in the current database.[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    'Debug.Print rs.Fields(0).Name[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    While Not rs.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        r = rs.Fields(0)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        rs.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Wend[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim db As DAO.Database[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim rst As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim fld As DAO.Field[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim s As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Set db = CurrentDb[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Set rst = db.OpenRecordset("Table1")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    rst.AddNew[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rst!Computer = r[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rst!Date = Now()[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    s = DLookup("[Name]", "[Asset Register]", "RIGHT([Asset Number],4)=" & r)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rst!Name = s[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    rst.Update[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    [/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

 

[nIGHTmAYOR]

remove brackets from table name , ie :

[FONT=Times New Roman][SIZE=3]s = DLookup("[Name]", "Asset Register", "RIGHT([Asset Number],4)=" & r)[/SIZE][/FONT]

 

[nIGHTmAYOR]

also do note that it is not wise to have spaces in your table names.
at least use an underscore _