How do you get a combo box to auto-lookup its value?

[NeoZakz]

I have the following code in VB:

Private Sub days_after_AfterUpdate()
If IsNull(Me![Combo119]) Then
Me![Date Due] = DateAdd("d", Me![Seq], Me![Date Due])
Else

DLookup("[Miledate]", "Mile1", Me![Date Due] = Me![Combo119]) = Me![Date Due].Value = DateAdd("d", Me![Seq], Me![Date Due].Value)

End If
End Sub

In the Dlookup part the Me![Combo119] wants a specific item from the combo box. Im trying to get it to read whats there currently and use that as the base to get the rest. Is there a way to make the Combo box see whats inside it and use it to do the rest of the lookup?

Thanks for any help in advance.

[Fornatian]

What is the objective of this code?

What you have at the moment will not work because you have

DLookup("[Miledate]", "Mile1", Me![Date Due] = Me![Combo119]) = Me![Date Due].Value = DateAdd("d", Me![Seq], Me![Date Due].Value)

set up like this:

A = B = C

this will not work but this will:

A = B
B = C

_________________________________________

DLookup("[Miledate]", "Mile1", Me![Date Due] = Me![Combo119])

should be:

DLookup("[Miledate]", "Mile1", "[Date Due] = #"&Me![Combo119]&"#")

If you are looking for the Miledate value from table Mile1 where Date Due is equal to value in Me![Combo119],where the value is a date.

It is easier to base the form on a query including the two tables joined by Date Due which will automatically pull the value through without any coding.

Again without knowing what you are trying to achieve it is hard to advise suffice to say that you may also have your code in the wrong place as you are altering field values in the AfterUpdate event, this fires once the form has been saved, you may wish to move these calculations into the BeforeUpdate event.

[NeoZakz]

Oh sorry here let me explain.

The code is supposed to check a combo box for a null value and if it has a blank then it does the dateadd by taking the number input into a date change box. If it is not blank its supposed to take the event from the combo box and look up the date that the event is due and set that as the Date Due then it takes the date from that and adds the date change numb.

[Fornatian]

The code is supposed to check a combo box for a null value and if it has a blank then it does the dateadd by taking the number input into a date change box.

If it is not blank its supposed to take the event from the combo box and look up the date that the event is due and set that as the Date Due then it takes the date from that and adds the date change numb.

here is one way of achieving what you need:

If IsNull(Me![Combo119]) Then
'if null then get value using another indicator
Me![Date Due] = DateAdd("d", Me![Seq], Me![Date Due])

Else
'if not null
Me![Date Due] = DateAdd("d", Me![Seq],
DLookup("[Miledate]", "Mile1", "[EventField]='"&Me![Combo119]&'")
End If
End Sub

Assuming that Me!Combo119 is a text field, if it isn't you will need other syntax.

Dlookup is not the best solution. you can get the value from the Mile1 table automatically pulled thru by including the table in the forms recordsource and linking the fields together. You can then include a hidden field with the event date, then use that in the formula above instead of having to retrieve it using dlookup which is memory intensive, especially if this is going to be part of a linked db.

You may not need to save the output at all. If you have all parts of the formula saved you can calculate that value at any time using a formula. If A x B = C, there is no point in holding C because you know you can always calculate it by way of A x B.

If you do need to save it you should move the entire code to the BeforeUpdate event or other such event as you may find yourself stuck in a loop of BeforeUpdate > AfterUpdate('dirtying' the form with data) >(change of data)BeforeUpdate > AfterUpdate etc..

Does that help?
Hope that helps.

[NeoZakz]

That looks like it might work, but Ill also give that other idea you suggested a try as well. That might actually work easier then what I've been trying to do.


Thanks alot!

[NeoZakz]

I got it working, what I ended up doing was creating an extra hidden text field and had it read off of the Combo119 that way it took only the word in there at the time an stored it here. Then I had the Dlookup read from that box instead it works now just have to figure away to make it autorefresh but that shouldnt be to hard. I got the idea to do the extra text field from your idea of putting in the hidden text field.


Thanks alot!

Been working on that for a few days knew it shouldnt have been that hard heh