Solved: DLookup question

[akn112]

Does anybody know if this code will work

DLookup("[Actions]", "History", "[E_Number] = Me!Form![PROD_TIDECHDR]![E_CODE]") = Me![FAO_Limit]!Form![combo64].Text

What i want to do is i have dropbox called combo64, a table called history. And there is a combo box. When a user clicks on the combobox, i want it to search for the E_NUMBER and match it. When it does, i need it to Delete the old Actions and replace with the words in the combobox

Edited 19-Jan-07 by geekgirlau. Reason: Make title more explicit

[geekgirlau]

Welcome to the Board!

I've taken the liberty of changing your title to indicate what your problem is - you'll get a lot more responses this way.

For the Dlookup, try this:

DLookup("[Actions]", "History", "[E_Number] = " & Me!Form![PROD_TIDECHDR]![E_CODE])

I'm not clear on what you need here - can you give us more information? If possible, create a new database with just the form you are referring to and any tables and/or queries you need to populate that form. Then you can attach it to a post so we can see what the issue is.

[akn112]

Thanks! Actually, i can't post the program cuz its like proprieter information... So ill try to explain it more clearly. I created a table with two columns E_NUMBER and ACTIONS. And i have a form which isn't linked (not sure how:dunno ) There's a combo box in the form, and whenever a user selects one of the options in the box (for a particular equipment as the form updates everytime u select an equipment from a list). Each equipment has its own E_NUMBEr, and i need to find a way to record the E_NUMBER and the ACTION selected in the table.

[OBP]

akn112, what do you mean when you say "And i have a form which isn't linked (not sure how:dunno )"?
If the Form is based on the Table, or better still a based on a Query that is based on the table, then the data will automatically be recorded.
The Combo box can be used to either go to a record in the Form's "Recordset" or it can jsut be used to list the E_NUMBER for the user to select, which is then put in to the E_NUMBER Field on the Form.

If you can give me a list of the Fields in your E_NUMBER Table and all you want in the "Actions" Table is the E_NUMBER and ACTION then I will create the Form and any necessary queries for you.

[Imdabaum]

DLookup("[Actions]", "History", "[E_Number] = Me!Form![PROD_TIDECHDR]![E_CODE]") = Me![FAO_Limit]!Form![combo64].Text
Correct me if I'm wrong but I think you might get an error trying to set a DLookup function = [data]. What you may want to do is check if DLookup returns a value for the criteria you have entered and then
(pseudo VBA code)

if (DlookupreturnsAMatch) Then
Set the Action for that record = Me![FAO_Limit]!Form![combo64]
End if


It looks like the combo64 is listing the Actions, since you are trying to set the DLookup("[Actions]", "History....)= combo64.text. Is that right?

[akn112]

Correct me if I'm wrong but I think you might get an error trying to set a DLookup function = [data]. What you may want to do is check if DLookup returns a value for the criteria you have entered and then
(pseudo VBA code)

if (DlookupreturnsAMatch) Then
Set the Action for that record = Me![FAO_Limit]!Form![combo64]
End if


It looks like the combo64 is listing the Actions, since you are trying to set the DLookup("[Actions]", "History....)= combo64.text. Is that right?

Yeah...at first, i wasnt sure if the Dloopup would work that way. HOwever, im not aware of any other function that would point to that specific cell and change it. In C++ (what im familiar with) it's just

array_name[index]= combo_64

my logic therefore says

table_name[index] = combo_64.

However, i dont think this is the right way of thinking.

[akn112]

OBP: Hmzzz... like, the program i have right now was set up by someone else, therefore im uncertain what i can change/remove and what i shouldn't. I don't think the form is based on the table. What i did was i added a new table to the database as a whole and added a new box that i wanted to link to that table. But im not sure how to do that.

[wiz47]

Correct me if I'm wrong but I think you might get an error trying to set a DLookup function = [data]. What you may want to do is check if DLookup returns a value for the criteria you have entered and then
(pseudo VBA code)

if (DlookupreturnsAMatch) Then
Set the Action for that record = Me![FAO_Limit]!Form![combo64]
End if


It looks like the combo64 is listing the Actions, since you are trying to set the DLookup("[Actions]", "History....)= combo64.text. Is that right?

Would this work on the AfterUpdate event of the Combo or would it set every record to that data?


Dim strsql As String
strsql = "UPDATE HisTableName SET HistableName.Hisfield = forms!Hisform!cboBoxName

DoCmd.RunSQL strsql

[akn112]

yeup, it would be after an update of the combo box

[Imdabaum]

Yes I think it would update every record, you'd have to include something like:


Dim strsql As String
strsql = "UPDATE HisTableName SET HistableName.Hisfield = " & Forms!Hisform!cboBoxName & " WHERE field.value = [some condition]"

DoCmd.RunSQL strsql