Dlookup an ID

[JeroenMioch]

Drives me completely out of my skull..



I have a dropdown box with two values in it on my form
I have to dlookup the ID from the listitems, but the table that holds them is not the recourdscource of the form.

Simple enough one might say but it will not give me the ID whatever i try..


This code returns the ID of one of the listitems :

Me.ctrlBuildingManagementID = DLookup("[BuildingManagementID]", "[tblBuildingManagement]", "[BuildingManagement] = 'ListItemNumber1'")

But i want the dlookup to return the value of the ID depending on the Building manager so i tryed this (and many, many other variants)

Me.ctrlBuildingManagementID = DLookup("BuildingManagementID", "tblBuildingManagement", "[BuildingManagement] = '" & Forms![frmObjecten]![kzlBuildingManagement] & "'")

kzlBuildingManagement is text and so is BuildingManagement in the table

Where am i going wrong here ?

[orange]

see if this helps.

If it doesn't , please post a copy of your database afte removing anything confidential.
Good luck.

[JeroenMioch]

Hi Orange,

Database is too large to post unfortunately.
Normally you try to find some data based upon an ID number. Now im trying to get an ID number based upon data.
The ID number is an autoID field of course..

When i try this code and replace the variables to my situation :

DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)

Im getting a data type mismatch error

[orange]

Now im trying to get an ID number based upon data

Your DLookup is trying to return the CustomerID.

the code, in English, is --get me the CustomeId from Table "Orders" where the OrderId is equal to the OrderId control on form Orders

What exactly do you want to return? Can you describe your request in plain English?

[JeroenMioch]

Yes the auto ID number is what im trying to get.

I have two Building management persons

BuidingManagementID BuildingManagment

1 Mr. X
2 Mr. Y

I can select either of them using my dropdownbox (kzlBuildingmanagement)
In a textfield (ctrlBuildingManagementID) i want to show the ID number of the manager on my form (frmObjecten)

I need this ID number to open up another form with their contact information.
I tried using Open Args directly but that also faled. My thought about it is that if i have the ID number (invisible) on my form, i can get the open args to work

[John_G]

Hi -

One thing I notice is in this line of code:

Me.ctrlBuildingManagementID = DLookup("[BuildingManagementID]", "[tblBuildingManagement]", "[BuildingManagement] = 'ListItemNumber1'")

You have ListItemNumber1 enclosed in quotation marks, making it a string. Is it a string or numeric? The data name ListItemNumber1 implies numeric. What is the data type of [BuildingManagement] in the table [tblBuildingManagement]?

John

[hapm]

Only a question: your combobox's record source isn't the tblBuildingManagement table, is it? Else you could change it to a lookup combobox having the ID as the first column with a width of 0 to hide it from the user, and use BuildingManagement as the second column. Easier to set up, and perhaps directly bindable to your forms record source field, all without any line of code, and faster than DLookup.

[Missinglinq]

If

DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)

gives you a data type mismatch error, then the Field OrderID is defined as Text, not as a Number, and the proper syntax for Text would be

DLookup("CustomerID", "Orders", "OrderID = '" & Forms![Orders]!OrderID & "'")

Linq ;0)>

[June7]

DLookup() should be last resort method. Other options:

1. include the customer table in form RecordSource - join type "Include all records from [primary table] and only those from [lookup table] that match", bind textboxes to [lookup table] fields and set them as Locked Yes

2. include [lookup table] in combobox RowSource (again with appropriate join type), expression in textbox ControlSource refers to columns of combobox

[JeroenMioch]

Hi -

One thing I notice is in this line of code:

Me.ctrlBuildingManagementID = DLookup("[BuildingManagementID]", "[tblBuildingManagement]", "[BuildingManagement] = 'ListItemNumber1'")

You have ListItemNumber1 enclosed in quotation marks, making it a string. Is it a string or numeric? The data name ListItemNumber1 implies numeric. What is the data type of [BuildingManagement] in the table [tblBuildingManagement]?

John

Hi John,

I understand the confusion. The where clause is not numeric, It returns the name of the building manager.
So i want to lookup the ID that belongs to the building manager on my form.

[JeroenMioch]

Only a question: your combobox's record source isn't the tblBuildingManagement table, is it? Else you could change it to a lookup combobox having the ID as the first column with a width of 0 to hide it from the user, and use BuildingManagement as the second column. Easier to set up, and perhaps directly bindable to your forms record source field, all without any line of code, and faster than DLookup.

No, the table that holds the building managers is not the recordsource of the form.
I can however set the combobox to two fields so it holds the ID number. However ten the user gets to see the ID number and this is unacceptable. Making it this way would solve the problem but visually its a no-go to have two coloumns in one combobox unless its absolutely nessecary.

[June7]

As hapm described, set multi-column combobox to hide the ID field.

RowSource: SELECT ID, otherfield FROM tablename;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"

User does not see the ID but ID is saved to record.

[JeroenMioch]

DLookup() should be last resort method. Other options:

1. include the customer table in form RecordSource - join type "Include all records from [primary table] and only those from [lookup table] that match", bind textboxes to [lookup table] fields and set them as Locked Yes

2. include [lookup table] in combobox RowSource (again with appropriate join type), expression in textbox ControlSource refers to columns of combobox

I am trying to grasp what you are saying.
The recordsource of my form is tblOjecten, the recordsource of the combobox is tblBuildingManager.
My users need to be able to change the building manager at will. The changes will be saved in the table tblObjecten ofcourse.
The reason i have a separate table for the building managers is so when one or more of them leave the company, the users can open the table and edit the data.

[June7]

And you can do all that.

Combobox to select building manager into tblObjecten record.

With option 1 the form RecordSource will have tblBuildingManager info associated with value saved by combobox because of the join on tblBuildingManager ID PK/FK fields, bind textboxes to those fields to show on form.

With option 2 textboxes will display tblBuildingManager info from the combobox columns.

[JeroenMioch]

As hapm described, set multi-column combobox to hide the ID field.

RowSource: SELECT ID, otherfield FROM tablename;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"

User does not see the ID but ID is saved to record.

Right it seems that has worked
Now arrises another problem. How can i get the value of the ID from the dropdownbox for use with open args ?