Ok this will be a doozy. Right now I have a form where work done in the
plant can be entered (Production Form). At this moment all quotas must be
manually entered into the form (fine for me as I have most memorized). What
I would like to do is have the quota field automatically populate based on
the item# and machine type (there are 9).

I know to get started I will have to create tables for each type of machine
that lists the item#'s and the standard. Then I am pretty sure I have to
build some kind of code telling access where to grab the information based on
the entry in the machine field of the form.

Am I on the right track? If so could someone give me an example or starting
point for the code?

Jim,

I used your code but I recieve a type mismatch. All fields are numeric
except the Item# it is a combo of text and digits. The only other thing that
my be causing the error is in the combo box for machine. I have two columns,
one numeric ID the other the machine name. I have made the first column
invisibile as most data entry personnel will not know the machine ID only the
name of the machine. Your help is appreciated.

"JimBurke via AccessMonster.com" wrote:

> Sounds like you're on the right track. As long as the same combination of
> machine # and item # always yields the same quota, you would want a new table
> with MachineID, ItemNumber and Quota. Just populate the table with all the
> combinations possible, e.g. if you had 3 machines and two items, you'd end up
> with something like this in the table:
>
> MachineID Item # Quota
> 1 1 50
> 1 2 100
> 2 1 75
> 2 2 90
> 3 1 40
> 3 2 500
>
> I don't know how data is typcially entered on your form,so how you implement
> the code on the form depends on that. I don't know if you care if the quota
> is filled in as soon as machine # and item # are filled in or whether that
> can wait. This code assumes they enter machine #, then item #, and you want
> quota filled in right after item # is filled in. Put something like this in
> your ItemNumber BeforeUpdate event proc:
>
> Dim myQuota as Long
>
> If IsNull(MachineNumber) then
> msgbox "Please enter machine number before Item Number"
> Cancel = True
> exit sub
> End If
>
> myQuota = Nz(DLookup("Quota","tblQuotaLookup","MachineID = " & MachineID &
> _
> " AND ItemNumber = " & ItemNumber),-1)
> If myQuota = -1 then
> msgbox "Could not find quota value for this combination of MachineID
> and Item Number."
> Else
> quota = myQuota
> End If
>
> You'd need to replace all the field names with the appropriate ones from your
> DB. This also assumes that all fields are numeric. If any of them ever have
> any non-numeric characters, you'd need to adjust the code, e.g.if MachineID
> wasn't always numeric then in the DLookup you'd have to surround the machine
> ID value with quotes:
>
> "MachineID = '" & MachineID & "' AND ItemNumber....
>
> If quota wasn't numeric then you'dhave to change the myQuota field to a
> String type and change the Nz value from -1 to vbNullString.
> That's a single quote after MachineID = and also just before AND ItemNumber..
> .. I wouldn't think you would have to worry about any of those fields having
> a quote in them so you should be safe with that.
>
> Hope this is on the right track for what you're looking for.
>
>
> Jamie Dickerson wrote:
> >Ok this will be a doozy. Right now I have a form where work done in the
> >plant can be entered (Production Form). At this moment all quotas must be
> >manually entered into the form (fine for me as I have most memorized). What
> >I would like to do is have the quota field automatically populate based on
> >the item# and machine type (there are 9).
> >
> >I know to get started I will have to create tables for each type of machine
> >that lists the item#'s and the standard. Then I am pretty sure I have to
> >build some kind of code telling access where to grab the information based on
> >the entry in the machine field of the form.
> >
> >Am I on the right track? If so could someone give me an example or starting
> >point for the code?
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200907/1
>
>