using a varible in a dlookup

[needhelplol]

i don't know if this is possible in vba,

to help here's a bit of background, i'm trying to creating an access replacement for a paper based log, so to speak, for a restaurant,
i have a few tables, forms some with sub forms and some querys.

one sub form has checkboxes to represent tables (based on the seating plan) each booking is linked to a table or more hence tick boxes, i want to be able to see if a table is all ready being used before it is allowed to be selected,
my problem is, i normally use php and mysql, where i could do

on clicking on check box (set as varible $box = table number) run query etc.

mysql_query("SELECT $box FROM tables WHERE ($box = 'yes')");

then if results found:- error table is in use

in my vba

private sub ctr1_onclick()
    tcheck(1)

    (more code to do with seats etc.)

end sub

public sub tcheck(box)
    check = DLookup(box, "qbooking", "yes")
    Text14.Value = Text14.Value + check 'error checking
    
    If check = Null Then
        MsgBox ("OK") 'error checking
    Else
        MsgBox ("This table is in use")
        
    End If
    
End Sub

in the query - 'qbooking',
booking_id, date, then tables 1->37,
123, 02-02-08, yes, (all other tables 2->37 =null)

in text14 it gives the value of box (this case '1') not the 'yes' i was hoping for, i'm using a query instead of using a sql query statement, thinking it would be easier...

if it is not possible, this there a way i could do this with a bit of reorganizing?

thank you for any response and for the time to read (long winded i know)

 

[JoeAtWork]

I think something like:

    check = Nz(DLookup("TableNo", "qbooking", "TableNo=" & box),0)
        
    If check = 0 Then
        MsgBox ("OK") 'error checking
    Else
        MsgBox ("This table is in use")
    End If

Note - you didn't say the name of the field in qbooking that has the Table Number, so I made up the field called TableNo.

 

[needhelplol]

Thanks but the problem is i have 37 checkboxes, and don't want to write the code 37 times,

sorry i missed a bit on my last post

 check = DLookup(box, "qbooking", box & "=yes")

so hoping for . dlookup ( varible, "QUERY", varible = "YES" )

thank you for the quick reply

 

[PHV]

So, in qbooking you have 37 fields named table1, table2, ..., table37 ?
Have a look here:

http://r937.com/relational.html

Anyway:
varible = "table" & box
check = DLookup(varible, "qbooking", varible & "=yes")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[needhelplol]

some good reading, made a start...

tried your answer,
renamed my field names to 'Table1-Table37' (made sense as to how you put it) rather than just 1-37

but now get a cancil prev operation error, any ideas? i don't see what would make that error

thank you for your help

 

[PHV]

What is qbooking ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[needhelplol]

qbooking = a (access) query (bid, date, status(if table is open,paid etc), then table1-table37)
first three items come from table called Booking and the rest from Table called Tables link by bid

hope that helps

 

[needhelplol]

also in the Table!Tables, my Table1-37 are yes/no's

 

[PHV]

What is the SQL code of qbooking ?

 

[needhelplol]

qbooking =

SELECT Booking.BID, Booking.Date, Booking.Status, Tables.Table1, Tables.Table2, Tables.Table3, Tables.[4], Tables.[5], Tables.[6], Tables.[7], Tables.[8], Tables.[9], Tables.[10], Tables.[12], Tables.[13], Tables.[14], Tables.[15], Tables.[16], Tables.[17], Tables.[18], Tables.[19], Tables.[20], Tables.[21], Tables.[22], Tables.[23], Tables.[24], Tables.[25], Tables.[26], Tables.[27], Tables.[28], Tables.[29], Tables.[30], Tables.[31], Tables.[32], Tables.[33], Tables.[34], Tables.[35], Tables.[36], Tables.[37]
FROM Booking INNER JOIN Tables ON Booking.BID = Tables.BID
WHERE (((Booking.Date)=Date()) AND ((Booking.Status) Not Like "Paid"));

i created that sql using the wizard then added the where in sql view

just really confused things...
i read the post in this forum on this error, done this single quote thing, got a syntex error, removed them and got the right result, i only had tick box 1-4 linking to this function, added box5(table5 tick box), tried that and got the cancil operation error again, *really confused*

 

[PHV]

You haven't renamed all the 37 fields ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[needhelplol]

lol good point
will give a progess report

 

[needhelplol]

back where i started, canciled op error...

 

[needhelplol]

some reason working now...

but... every table returns then same result, in use or not

 

[PHV]

Again, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[needhelplol]

fixed it using nz()
thank you for your help