Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-22-2002, 09:15 AM   #1
NeoZakz
Junior Member
 
NeoZakz's Avatar
 
Join Date: Jul 2002
Location: Florida
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
NeoZakz
Question How do you get a combo box to auto-lookup its value?

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.


Last edited by NeoZakz; 07-22-2002 at 12:53 PM.
NeoZakz is offline   Reply With Quote
Old 07-22-2002, 12:34 PM   #2
Fornatian
Dim Person
 
Fornatian's Avatar
 
Join Date: Aug 2000
Location: England
Posts: 1,396
Thanks: 0
Thanked 4 Times in 3 Posts
Fornatian is on a distinguished road
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.
Fornatian is offline   Reply With Quote
Old 07-22-2002, 12:45 PM   #3
NeoZakz
Junior Member
 
NeoZakz's Avatar
 
Join Date: Jul 2002
Location: Florida
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
NeoZakz
Objective

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.

NeoZakz is offline   Reply With Quote
Old 07-22-2002, 11:13 PM   #4
Fornatian
Dim Person
 
Fornatian's Avatar
 
Join Date: Aug 2000
Location: England
Posts: 1,396
Thanks: 0
Thanked 4 Times in 3 Posts
Fornatian is on a distinguished road
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.
Fornatian is offline   Reply With Quote
Old 07-23-2002, 03:12 AM   #5
NeoZakz
Junior Member
 
NeoZakz's Avatar
 
Join Date: Jul 2002
Location: Florida
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
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 is offline   Reply With Quote
Old 07-23-2002, 09:44 AM   #6
NeoZakz
Junior Member
 
NeoZakz's Avatar
 
Join Date: Jul 2002
Location: Florida
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
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

NeoZakz is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 12:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World