Auto Populate Fields in a Form

[Jamie Dickerson]

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?

 

[JimBurke via AccessMonster.com]

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]

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.