Dlookup with Boolean Field

[BofRossClan]

I am populating my "Invoice event" table from my master "Accounts" table. Everything works great EXCEPT for the boolean field [Transfer]. Dlookup looks up the field, but will not set the form field to that value.

This is my code. Should I do something different with the boolean field?

Dim intCode As Integer
Dim strName As String
Dim strAccount As Variant
Dim booTransfer As Boolean
    
intCode = Me![cmbBilltoName]
strName = DLookup("[BillToName]", "Accounts", "[Counter] = " & intCode)
strAccount = DLookup("[Account]", "Accounts", "[Counter] = " & intCode)
booTransfer = DLookup("[Transfer]", "Accounts", "[Counter] = " & intCode)
    
    Me![Building] = Me![cmbBuilding]
    Me![BillToName] = strName
    Me![ContactPerson] = strName
    Me![Account] = strAccount
    Me![Transfer] = booTransfer

 

[WildHare]

Is your TRANSFER field a YES/NO? If it's not, the code is having trouble figuruing out whether or not the value is TRUE or FALSE, which are the only permissable values for a boolean variable.

For example, of the field's value is "DONE" or "OK" or "Wichita", and you set a boolean to it, is DONE true or false? And if Wichita is FALSE, can Des Moines be anything but TRUE?

See the problem? There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BofRossClan]

Yes, the "transfer" field in both tables is Yes/No.

 

[WildHare]

Why don't you just bind the form's TRANSFER to the table's TRANSFER?

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BofRossClan]

You mean with a query? I don't want to do that, because the info in "Accounts" changes, and could be deleted, and I want to keep a record on each invoice for future reference.

 

[WildHare]

You can still put the field in your query, and mark the text box on the form as LOCKED...I think that would be your best bet. In fact, there's really no reason why you can't have all of your "parent" fields in the query and marked thusly. It allows you to display the parent field values, without worrying about someone inadvertently changing one of them.

I do this quite a lot, and have developed a visual standard for "read only" fields that are not to be changed. Then every time a user sees one of these fields on a form, they know they aren't editable. Makes for cleaner forms and less coding.

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[BofRossClan]

Yes, but if the information in my master "Accounts" table gets changed, and names, account numbers etc. change from year to year, I don't want it to change the data in my "Invoicing Events" table. I don't want it linked, I want it copied, so that when I look at it 2 or 3 years down the road, it shows the information as it was at the time it was invoiced, not the current information.

 

[WildHare]

You've kind of lost me on this (which isn't all that surprising...) You have an INVOICE EVENT table, which you are populating with "current" values from the MASTER account table. Fine.

Open the Master table.
Open the Invoice Events table.
Position to the MASTER record you want to "copy" to the I/E table.
Set I/E fields = Master fields
Post record to I/E table.

If in three years down the road, MASTER account "X" has Transfer changed from "YES" to "NO", that won't affect the record you stored three years earlier that had I/E!Tranfer = "NO" since Master!Transfer = "NO" at the time.











There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm