DLookup Help?

[gmatriix]

Hello All,

What I am trying to do is when I choose a value in a drop down this will populate the the next field called "cutoff" based off of the drop down.

Is there a easy way to do this in VBA? I tried this but it is not working

Private Sub Cutoff_AfterUpdate()

Dim varX As Variant

varX = DLookup("[Cutoff]", "qMachGroup", _
    "[Machine Size]= " & Forms!Product!Combo76)

End Sub

Any Ideas?

 

[pbaldy]

I would use this instead, if appropriate (more efficient):

http://www.baldyweb.com/Autofill.htm

If it doesn't fit the situation, I don't know what "not working" means. Perhaps the size is text?

http://www.mvps.org/access/general/gen0018.htm

 

[gmatriix]

Thanks,

I tried this below and I get runtime 2471.....sorry I'm still learning.....

Private Sub Cutoff_Click()
Dim varX As Variant


varX = DLookup("Cutoff", "qMachGroup", "[Machine Size] = '" & Me.Combo76 & "'")

Me.Cutoff = varX
End Sub

The reason I need code is because I need for the user to overwrite the value in the cutoff field...

Ideas?

 

[michaeljryan78]

DLOOKUPS are expensive. They iterate every record of a continuous form on the Load event. You may want to adjust the recordsource of the Autopopulating field to be "SELECT whatyouwant FROM yourtable WHERE whatyouwant = " & Forms!Product!Combo76.

 

[pbaldy]

Not having them memorized, what is error 2471? Is the size field numeric or text? Is the other link not applicable?

 

[gmatriix]

Thanks michaeljryan78

That make sense however, I need the user to be able to overwrite the field. The drop down will just reset it to default.

So they can use the drop down...but if they wanted to change the amount they can...

Thats why Im trying to do it in code....trying anyway

any suggestions?

 

[pbaldy]

Yes, read my link all the way to the bottom.

 

[gmatriix]

Pbaldy,

I changed it to this

Private Sub Cutoff_Click()
Dim varX As Variant


varX = DLookup("[Cutoff]", "[qMachGroup]", "[MachineSize] = '" & Me.Combo76 & "'")

Me.Cutoff = varX
End Sub

No error now...but still nothing...what event should I have this on?

Whenever I change what is in the drop down...it should populate the field "Cutoff"

Ideas?

 

[pbaldy]

You want the after update event.

 

[gmatriix]

Ok,

I put it on the afterupdate event on the cutoff field and I'm am still getting nothing....

Is there something wrong with my code?

 

[pbaldy]

Have you tried the more efficient:

Me.Cutoff = Me.Combo76.Column(2)

Replacing 2 with the appropriate column? By the way, you're using the events of the Cutoff field; it needs to be the combo's after update event.

 

[gmatriix]

pbaldy,

I tried Me.Cutoff = Me.Combo76.Column(2) and I did not get anything....

not sure what is wrong...I tried

Private Sub Combo76_AfterUpdate()
Dim varX As Variant

varX = DLookup("[CutoffWidth]", "[qMachGroup]", "[MachineSize] = '" & Me.Combo76 & "'")
Me.Cutoff = varX
End Sub

On the combo box and it populating 0 in the Cutoff field....

 

[pbaldy]

Did you adjust the number to match the column (remembering that the property is zero based)? Can you post the db here?

 

[gmatriix]

Here is just an example of what I have,

The db I have is too big

Thanks

 

[pbaldy]

It would be this since you only have 2 fields:

Me.Cutoff = Me.Combo0.Column(1)

But that errors because it's trying to put a text value like "1 in and below" into a numeric field. Perhaps another field needs to be added?

 

[gmatriix]

Well what I want to return is the CutoffWidth which is numbers like 0.145....this number should populate in the cutoff field..

 

[pbaldy]

Add that to the row source, change the column count and widths properties and change the 1 to a 2 in my code.

 

[gmatriix]

Im sorry,

I will not be about to overwrite the Cutoff field if I put it in the row source....that why I was trying to create a vb code for it.

I don't mean to be pain with this....

 

[pbaldy]

The ROW source, not the CONTROL source. The query that provides the dropdown options.

 

[gmatriix]

Can I send that testdb12 back with your changes...so I can make sure I have it right?