DLookup and Update SQL...probably same fix

[snayjay]

Dlookup Part
Can't figure out what I'm doing wrong, am I using a protected word or something or is it something with an autonumber.

IM.Value = DLookup("[IM]", "IMMtbl", "[IMID] = " & SENTLIST.Value)

IM.value = textbox on the form (updatable not locked)
[IM] Memo field on IMMtbl
[IMID] Autonumber Primary Key on IMMtbl
SENTLIST.value - sentlist is a listbox on the form, the bound column refers to the autonumber column of the queried table

I think the same problem I'm having here is also affecting my UPDATE SQL code as well...

UPDATE SQL part

UPDATEsql = "UPDATE IMMtbl SET IMMtbl.SD = -1 WHERE IMMtbl.IMID = " & SENTLIST.Value

SD is a yes/no column and IMID is the autonumber and SENTLIST.value is the same as before


~Snayjay

 

[PWise]

what error are you getting

 

[PHV]

And hopefully SENTLIST is not MultiSelect.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snayjay]

Ok sorry I wasn't so clear before, I wrote the original post while in the middle of a CSA Class (Client Support Admin) for work.

The following code is placed on the Listbox(SENTLIST) doubleclick event.

IM.Value = DLookup("[IM]", "IMMtbl", "[IMID] = " & SENTLIST.Value)

I have a listbox with 2 2-line values...of course this could be 5 2-line values depending on what the row source query pulls.

Listbox ------------------------------
| Name | Message |
| Name | Message |
|_________|____________|

This listbox is generated from a query that pulls 5 values from a table.

1)RID text users netID jeffrey.loehr
2)IM text(memo) message Hey what's up!
3)SD yes/no whether or not 1 user wants to delete this
4)SID text users netID rod.lyons
5)IMID autonumber primary key 24


Below the listbox I have a memo(textbox) called IM

All I want is when the user double clicks the listbox it calls dlookup on the table that the listbox is generated from and gets the IM.value from the recordset that the user chose from the SENTLIST(listbox). I'm used to a listbox.value being the boundcolumn from the properties and in this case I've chosen 5. I'm only showing 2 columns (column count) on the listbox, but this should matter... or at least hasn't in the past on other projects (all have 'show' property checked in the query.

And no... it's not a multi-select listbox.

The error I'm getting is

Run-time error '3075':
Syntax error (missing operator) in query expression '[IMID] = '.

I know if the WHERE part of a dlookup function is a string you have to use "[IMID = '" & SENTLIST.value & "'")

and if it's a date you would use "[IMID] = #" & SENTLIST.value & "#")

but this is an autonumber which to my knowledge you don't use anything.

Any help would be appreciated.

 

[PHV]

I'm only showing 2 columns (column count) on the listbox
ColumnCount should be 5 !
To hidden some columns play with ColumnWidths (eg "1 cm;2 cm;0;0;0")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snayjay]

And that's why you are The Man! Have a star!