Get Checkbox Value from table not associated with form

[josie]

okay here goes... my first post on a forum other than my uni site.
I am building (trying to anyway) an access db. I have a invoice form that is based on a table (invoice table) and I have a checkbox field in another table (customer table) that I need to get into the invoice form so that I can do some visislbe and enable stuff with other fields on the form based on whether the field in the customer table is checked or not.
I have tried the DLookup function as follows;

'check if the customer is a memeber
Dim varIsMember As Variant
varIsMember = DLookup("[Member]", "tblCustomer", "[CustomerNo] = "
& Me!CustomerNo)
varIsMember = DLookup("[Member]", "tblCustomer", "[CustomerNo] =" _
'& Forms![frmInvoiceNew]!CustomerNo)

' if the customer is a member then enable disount else disable discount field
If varIsMember = "0" Then
Forms!frmInvoiceNew.frmLineItemTotals.Form!DollarD iscountValue.Enabled = True
Else
Forms!frmInvoiceNew.frmLineItemTotals.Form!DollarD iscountValue = 0
Forms!frmInvoiceNew.frmLineItemTotals.Form!DollarD iscountValue.Enabled = False
End If

but I keep getting a Error 2001 previous action canceled.
I have spent all night (and I mean all night, 4.30am!) and still can't figure out what I am doing wrong.
Please help a strung out student.
Many thanks, j.

 

[G37Sam]

well here's what i would do.. see where the error is coming from to start with.. as in which "previous action canceled". Check if VarIsMember is giving you the proper value.. do so by outputting its value in a msgbox before jumping to the comparison. If it has been outputed correctly then jump to the other section of your code.

Now from what i can see, you've defined VarIsMember as variant.. why not define it as boolean instead. Then instead of: If varIsMember = "0" use If varIsMember = false

You're also getting the VarIsMember value twice.. why is that? or is it a typo?

 

[missinglinq]

3ammerksamer is correct! The value for varIsMember will never equal "0." "0" indicates a string with a single character 0. The checkbox will return 0 (zero) if the Member checkbox is unchecked, and -1 if the checkbox is checked. So your code should be:

' if the customer is a member then enable disount else disable discount field
[B]If varIsMember = -1 Then[/B]
Forms!frmInvoiceNew.frmLineItemTotals.Form!DollarD iscountValue.Enabled = True
Else
Forms!frmInvoiceNew.frmLineItemTotals.Form!DollarD iscountValue = 0
Forms!frmInvoiceNew.frmLineItemTotals.Form!DollarD iscountValue.Enabled = False
End If

You also need to check the datatype of your field [CustomerNo]. If the datatype is Numeric,

varIsMember = DLookup("[Member]", "tblCustomer", "[CustomerNo] = "
& Me!CustomerNo)

should work. If, however, [CustomerNo] is defined in the table defs as Text, you need different syntax:

varIsMember = DLookup("[Member]", "tblCustomer", "[CustomerNo] = '" & Me!CustomerNo & "'")

This may or may not resolve your problem, but it needs to be addressed. The thing you have to remember when debugging code is that you'll only recieve one error message at a time. If you have multiple errors, you have to fix one before the next one will show.

One question; exactly where/how is this code being executed? This in itself may explain the particular message you're getting at this time.

 

[josie]

Checkbox Replies

I like cats

The code is being executed from an On Current event.
But your explaination was invaluable - thanks to those you gave me some feedback - so much appreciated. I like this forum stuff!!
I will try suggestions and fingers crossed - else you could be hearing more from me.
Regards, josie

 

[josie]

Checkbox Dlookup - still problems

Ah, but you see here in is where the problem lies.
As novice to vba I can't even figure out how to check what 'operation has been previously canceled'.
Let alone get the dlookup variable give me a response in a message box.
Previous operating canceled - I have been thru my code and I don't have any other events on on the form other than some some click events on some of the fields - but not the field the form first gets focus on and that's about it.
And advice as to how I id what 'previous operation was canceled'? Or is this just streching the friendship.
Boy this is difficult!
josie

 

[boblarson]

'previous operation was canceled'?

When you get this, when using a DLOOKUP, usually it is because a null value is being returned.

I would use:

VarIsMember = Nz(DLookup("[Member]", "tblCustomer", "[CustomerNo] = '" & Me!CustomerNo & "'"),"")

 

[josie]

Life Saver!

Mr Larson,
if you weren't all the way in Oregon, US - I kiss you!!!!
Thankyou SOOO much - it worked!!
I have trolled sites, tried db connection suggestions and couldn't get anything to work. Just when I was about to give up you have revived my enthusiasm. What can I say...
U r a life saver!
1000 thanks,
josie

 

[boblarson]

Mr Larson,
if you weren't all the way in Oregon, US - I kiss you!!!!

I doubt my wife would be thrilled

Thankyou SOOO much - it worked!!
I have trolled sites, tried db connection suggestions and couldn't get anything to work. Just when I was about to give up you have revived my enthusiasm. What can I say...
U r a life saver!
1000 thanks,
josie

Glad to be able to help. Believe me, I've been in your place before and that error message is no help because it really doesn't say anything about what is actually happening. I had to figure that one out as well before and that's why I was pretty sure that it was what was going on.

 

[josie]

No probably not my husband either! Although given the hours he seen me staring at the screen I'm sure he would understand.
With any luck this has helped me progress things and he can have his wife back <grin>

Another one for you - if your're in the mood....

I want to set a variable and store that variable using an sql statement so then based on that variable value I can set some field properties in a form. But when I try and set the varialbe then check the result in a msgbox I get the SELECT statement in the msgbox not the results of the statement - huh?
Is this an easy one??

For example;

'Set a variable to find a value based on my current record
Dim myVar as Variable
myVar = "SELECT [Member] FROM Customers WHERE customerNo = " & me.customerNo

'check result of sql statement and if it works then I can do some things
MsgBox myVar

josie

 

[boblarson]

You can't get records back that way. You need to use a recordset for that.

Dim strSQL As string
Dim rst As DAO.Recordset

strSQL = "SELECT [Member] FROM Customers WHERE customerNo = " & Me.customerNo

set rst = CurrentDb.OpenRecordset(strSQL)

MsgBox rst(0)

rst.Close
Set rst = Nothing

 

[josie]

Awesome!
will pop that bit of code aside for a ron (latter on <grin>)
for the moment coding frantically and going great guns now that I have dlookup working.
gotta love this forum stuff - heaps more helpful than the uni one - just a bunch of students stumbling around in the dark.
While I would love to be able to help others out as you have done me, I expect that I will be doing most of the asking and not much of the answering - at least for now anyway.
thanks again,
josie