I am using Dlookup in a form to retrieve data from the table in a text box. My key fi

[irslan rafique]

Driver's database= table
Driver' Name= Text value
Main Query= name of form
Combo 102: Driver's Names are stored here
IC No: Numeric value

=DLookUp("[IC No]","[Driver's database]","[Driver's Name]=" & [Forms]![Main Query)]![Combo102])

Please assist me

[jimatqsi]

Irsian,
welcome to Bytes.com.

Please do not use the subject line to state your entire problem. Your subject got cut off and cannot be read.

Your problem appears to me to be a failure to put the driver name in quotes. Because it is a string value you must include it as a quoted string. Try this change.

=DLookUp("[IC No]","[Driver's database]","[Driver's Name]='" & [Forms]![Main Query)]![Combo102]) & "' "

Jim

[zmbd]

By, simply stating that your code "doesn't work," along with posting code that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted. Instead, please tell us what you were expecting to happen, what actually happened.

[irslan rafique]

Thanks for you prompt suggestion for being a new user
Thank you so much bro for answering on my issue. I used your code but its not retrieving the data. Passes only #Error. I did check all the field names and spelling mistakes but still receiving #Error.
Please assist me accordingly

[irslan rafique]

I am using this function on a form to retrieve text data from the table using combo box.

'[z{Stepped code, it will not work w/ direct cut and paste}]
=DLookUp("[IC No]",
   "[Driver's database]",
   "[Driver's Name]='" & 
      [Forms]![Main Query]![Combo285]) & 
         " ' "

Error is: #Error
Please help out me.
Thanks

[jforbes]

Hey There,
Using a Single Quote will give you crazy errors like the one you are experiencing. Spaces can also throw you for a loop, but they aren't as big as a problem as Single Quotes.
First thing I would do is rename [Driver's database] to[DriverDatabase] and rename the column [Driver's Name] to [DriversName]. You might also want to consider renaming your Form to [MainQuery]. Lastly, there is an extra space before your last single quote

If you were to rework all those things, the following might work:

Me.txtSomeTextbox.Value = DLookup("ICNo", "DriversDatabase", "DriversName='" & Forms!MainQuery!Combo285.Value & "'"

[zmbd]

#Error in a calculated control usually indicates a syntax error in the formula, formula itself returned an error, or the use of the formula is not appropriate for the control.

Don't use the dlookup() in a combobox - this will not work as expected

You must understand the nature of a combobox:
Customize a list box, combo box, or drop-down list box

Typically, one has a table with a one to many relationship with another table and you want to show the human readable text from the second table such as:

[tbl_parent]
[PK](autonumber)
[fk_comboboxlist](numeric(long))
{other fields}

[tbl_comboboxlist]
[PK](autonumber)
[description] (text(25)]

So you have a form such that:

{form title}
{label}   {label2}    {lable3}
{textbox} {combobox2} {textbox3}
 

You want the {combobox2} to show the
[tbl_comboboxlist]![description] value however because the
[tbl_parent]![fk_comboboxlist] is long the {combobox2} needs to actually have the [tbl_comboboxlist]![PK] as its value.

The simplest method is done by:
Set {combobox2} control source to [tbl_parent]![fk_comboboxlist]
Set {combobox2} row source type to "table/query"
Set {combobox2} row source to [tbl_comboboxlist]
Set {combobox2} bound column to 1
Set {combobox2} number of columns to 2
Set {combobox2} column widths to 0,1

The tutorial above will give much more detail.

What you have to keep in mind is that the bound column is the value that will be returned from the rowsource property to the control source. If it is blank, then your combobox is returning nothing. You need to have it set to the [field] of data in the rowsource that is required in your recordset.

It is very important that you understand that the CONTROL SOURCE is NOT the same as ROW SOURCE in a combo/list box. Normally the two are only related in that the bound column of the ROW SOURCE must return a value that is of the correct typecast as the field to which the cbo's CONTROL SOURCE is set (bound).

There is another method with multi-column record sources that you could use read thru this: How to select Records in an Access Subform from a Combo Box it's a bit of an overkill so don't worry if you don't follow somehthing, just ask back here and we'll clear it up (^_^)

[zmbd]

jforbes, looks like we cross posted.
I saw those same errors; however,

[IR]: retrieve text data from the table using combo box.

gives me the impression that irslan is attempting a look-up field at the form level.

I guess that is something we should confirm instead of me just guessing, (^_^)

@irslan: My last post is based on a guess that you are attempting to create a look-up field in your form.

Lookup fields at the query and form level are very common place and where their use is, as a best practice, limited to.

Unless you are going to publish to a SharePoint-Site then the following describes how many (and I dare say most access developers feel about): The Evils of Look-up Fields in Tables. Once again, IMHO, at the table level, the only legitimate time to use a look-up field is for SharePoint interactions.

[jforbes]

Right On. I didn't catch the ComboBox as the Target.

[irslan rafique]

Once again thanks for your prompt reply. Very sorry coz I am new to Access:
I have one main table where i m having few fields:
I made a query on main table and then made a form on query.
On the form i made a combo box where the drivers' name are called from another table and made them to save in main table. So i want, when i select driver's name from the combo box, it should retrieve IC No in a text box on a only form and at the same time show the same driver's name in the driver's name field.(its working)
But cannot retrieve IC no on selecting drivers's name.
Fields in main table:
Driver's Name (Text)
IC No(Numeric)

It is very humbly requested that please solve my problem.

Thanks

[zmbd]

irslan:
Then you have exactly the situation I've described in post#7.

Please follow the links therein and work thru it's tutorial section. It will describe step by step what you need to do.

If you still do not follow post#7.

then perhaps a more detailed explanation is required:
Access Tutorial 8: Combo Box Controls (PDF - Print and save (^_^) ) This article will show you everything you could possibly want to do with the combobox and perhaps more.

Respectfully, we will not do the work for you.

-z

[irslan rafique]

Thanks a lot bro for giving a clear view using a combo box. So skipping a combo box, i want to use a text box instead of combo box. User just write the name of driver and related details come out. Can u write that dlookup function for me to retrieve string from the table.
All other scenario is same.
Thanks and assist me.

[irslan rafique]

Hi,

GOT IT by playing the Dlookup, described u earlier,
FOR TEXT:

=DLookUp("[ICNo]","[Drivers]","[DriversName]='" & [Forms]![Main]![Text18] & "'")

FOR COMBO:

=DLookUp("[ICNo]","[Table1]","[DriversName]='" & [Forms]![Drivers Query]![Combo11] & "'")

Thanks indeed. It was my first experience to ask something on the forum. I am really grateful to both of you(ZMBD and JFORBES)for prompt assistance. Take care and bye