Expression Builder Tick Box DLookUp

[kilburfi]

Hi - I am not very adept at Access but have put together some tables, queries and forms and have, so far managed to get by. I have come to something which should be really simple but I am struggling.

1. I have a table called "CONTRACT NAMES AND NUMBERS" with a field called "REDUCED_USERS", this field is a checkbox (Yes/No in the table). This table houses all of the customers with their id numbers and basic info.
2. I have another table called "REQUESTS" which houses their orders. This also has a field called "REDUCED_USERS".

In my form "Amendment Request Tracking" I have tried to do a DLookUp in Expression Builder to check the box, per order, if the customer has reduced users in the "CONTRACT NAMES AND NUMBERS".

I have tried many variations and have just realised that this is probably because it is a yes/no field so may struggle with what to populate with (currenly nothing!).

My most recent variation of expression is (where NAD_NUMBER is the common field in both Tables and Form with relevant relationship):

=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]=[NAD_NUMBER]")

 

[pbaldy]

See if this helps:

http://access.mvps.org/access/general/gen0018.htm

 

[kilburfi]

Thanks Paul - when OKing this it gives me:

=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[NAD_NUMBER]=[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]")

However this still does not work. I am expecting that if the tick box is ticked in the CONTRACT NAMES AND NUMBERS table it will show as ticked in this form but that doesn't seem to happen.

 

[pbaldy]

That doesn't follow the syntax in the link, which would be the form style syntax.

 

[kilburfi]

It may be my missunderstanding but the answer I want isn't from a form it is to go in to a form. I am saying that the NAD_NUMBER in my current form is the same as the NAD_NUMBER in the table CONTRACT NAMES AND NUMBERS. The syntax in the reference doc seems to point to the target being another form - unless I have it the wrong way round. I am entering the expression in the control source for a tick box in my existing form.

 

[pbaldy]

The criteria comes from a form, does it not? Try

=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[NAD_NUMBER]= " & [NAD_NUMBER])

 

[kilburfi]

Paul - unfortunately none of these combinations have worked. I found something on the web about using columns and this worked well for one of my fields.

In the table I did a combo box which showed the customer number the customer name and whether they were cancelling. The customer number was column(0) the name was column (1) and the cancelling was column(2). When using the drop-down combo in the table I can see all three columns (I didn't change the column widths as I wanted to make sure they displayed.

In the Form I set the Customer Name field to have a control source of [NAD_NUMBER].[COLUMN](1). This then populates the form with the second column of the combo box from the table.

I have tried adding another field "Cancelling" to the form and setting the control source to [NAD_NUMBER].[COLUMN](2) which is the column that, in the table, shows whether they have cancelled but this doesn't work either. If I set this to be column 1 it shows the customer name in this field so why not column 2?

Losing the will to live now as I can't seem to get this to display. Even looking at the help file for Column it says you just start with 0 as the first one and then 1 for the next and so on.

Surely this is a simple task to show the data from another table in a form based on the entry of a field on the same form?

 

[pbaldy]

If it shows 1 but not 2, make sure the column count property of the combo is 3 (it isn't zero based like the column property).

 

[kilburfi]

Yes Paul it is 3 and all 3 show when viewing the table.

 

[kilburfi]

Paul - I have done this now with an update table query which updates the form source table so it now shows correctly in the form. I don't know why the Column(2) wasn't working as all set up fine but I have managed to get round it now. Thanks for all your help and advice. Fiona.

 

[pbaldy]

Happy to help Fiona, and welcome to the site by the way!