Creating a Lookup That Populates Data Entry Form

[DataDiva via AccessMonster.com]

Hello All,

I am building a Quality db in Access 07 for my work and would appreciate any
help that someone would be willing to offer so I can figure this out. My
experience in Access is limited to basic college courses and working out
problems through the use of these forums.

TblMain

Today's_Date - Date/Time
Job_Number - Text (PK)
Customer - Text
Assembly_Number - Text
RoHS - Text
REV - Text
Order_Qty -Number
Order_Status - Text
ID

TblMain has a 1 to Many relationship to tblDepartments

TblDepartments

Today's_Date - Date/Time
Department - Text
Job_Number Text
Customer - Text
Serial_Number - Text (PK)
Assembly_Number - Text
RoHS - Text
Inspector_ID - Text
Previous Operator - Text
Job_Function - Text
Process - Text
Qty-Assembled - Number
Defects_Present - Text

I created a query from TblMain which includes Job_Number, Customer, Assembly,
RoHS.
What I would like to do is have users scan the job_Number into an unbound
textbox, hit a Find Record button and have it populate these four fields on
the form. The record source for FrmDepartments is TblDepartments. Currently
the redundant fields are bound to both tables and I'm not sure if I should
just display that info on FrmDepartments or save them to the table.
TblMain/FrmMain will only be used for the originator of the job to put the
initial record in.

So far I have an unbound text in frmDepartments and a command button with
this code in the On Click event:

Private Sub cmdJobLookup_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Qry", dbOpenDynaset)
With rst
.FindLast "[Job_Number]=""& Me.JobNumber&"""
Me.JobNumber = !Job_Number
Me.Assembly_Number = !Assembly_Number
Me.Customer = !Customer
Me.My_RoHS = !ROhs
..Close
End With
Set rst = Nothing
End Sub

This is only populating the fields with the first record in the query. I need
it to look up the job number that the user will type in the textbox. Hope I
have been clear enough. I am open to any and all suggestions!

Thank you

CBH

 

[Jeff Boyce]

It all starts with the data...

You've described the table structure with field names, but I'm not yet clear
what the actual data is that's being stored.

[Today's_Date], a Date/Time value, I get.

[Customer], a Text value, I can't tell wether this is a customer name, a
customer ID or ????.

From your description, it looks like [Customer] is showing up in both
tables. Why? If there is a 1-m relationship, you'd only need [Customer] at
the level it was appropriate (but I can't quite tell yet what level that
is!).

How is it that tblMain is a "parent" to tblDepartments?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DataDiva via AccessMonster.com]

Hello Jeff,

Thank you for your quick response! The db will used to track defects for
Printed Circuit Boards.
TblMain is used to store the general information for a specific job number.
TblDepartments is used to store information about the inspection of specific
serial numbers (boards) throughout the production process of the job. If
defects are found on a board there is a separate table [tblDepartmentDefects]
that collects data that pertains to the defects on a particular board.
frmDepartmentDefects is opened by a command button on frmDepartments.

tblMain:

Job_Number - textbox where user will scan in job number

Customer - combobox used to store the customer name, the row source is a
table named tblCustLookUp

Assembly_Number - text box for the user to enter Assembly number

Rohs - combobox with value list

Rev - text box to enter revision number

Order_Qty - textbox to enter Qty

Order Status - combobox with value list

ID - Autonumber


TblDepartment

Department - combobox used to store department name row source is a table
named tblDepartmentLookup

Job_Number - textbox where users will scan in job number

Customer - textbox that will hopefully auto-populate customer name

Serial_Number - textbox where serial number will be entered

Assembly_Number - textbox that will auto-populate assembly number

RoHS - textbox that will auto-populate yes or no

Inspector_ID - Textbox

Previous_Operator - Textbox

Job_Function - combo box with value list

Process - combo box with value list

Qty_Assembled - textbox

Defects_Present - Combobox with value list

tblDepartmentDefects

Todays_Date

Department - Cascadng combo that populates the error_Code combobox (would
like this to autopopulate from frmDepartments and still populate error_Code
combo... not sure if it's possible}

Job_Number - textbox autopopulates from frmDepartments

Serial_Number - textbox autopopulates from frmDepartments

Qty_Defective - Textbox -Number

Error_Code - Combobox dependant on the Department choice

Location - textbox

Comment - textbox

ID - autonumber

Customer, Job_number, Assembly_Number and RoHS are repetitive fields at the
moment. They need to atleast be on frmDepartments as a visual aid for users.
I guess actually saving them in the table would violate the rules of
normailzation, so I will remove Customer, Assembly_Number and RoHS from
tblDepartments.

TblMain has a 1 to m relationship to tblDepartments, I have not set up any
parent/child relationship because I was not sure if I needed to or how to go
about it.


I hope I provided the info that you were looking for and it makes some sense!
Your help is much appreciated!

Courtney

 

[Jeff Boyce]

Courtney

See comments in-line below...

(I stopped after a while because I ended up with the sense that the data
model is more like a series of spreadsheets than a relational database ...
i.e., well-normalized.)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

Thank you for your quick response! The db will used to track defects for
Printed Circuit Boards.
TblMain is used to store the general information for a specific job
number.
TblDepartments is used to store information about the inspection of
specific
serial numbers (boards) throughout the production process of the job. If
defects are found on a board there is a separate table
[tblDepartmentDefects]
that collects data that pertains to the defects on a particular board.
frmDepartmentDefects is opened by a command button on frmDepartments.

tblMain:

Job_Number - textbox where user will scan in job number

Customer - combobox used to store the customer name, the row source is a
table named tblCustLookUp

Why store "name"? What do you do when you have two "John Smith" customers"?
Store CustomerID instead.

Assembly_Number - text box for the user to enter Assembly number

Rohs - combobox with value list

Using a value list in a combobox is certainly quick ... but is not
particularly flexible. Remembering to update the value list inside a
combobox, inside a form, is much more difficult for most folks that simply
using a lookup table and doing your maintenance in the table.

Rev - text box to enter revision number

Order_Qty - textbox to enter Qty

If you are recording quantity, this implies that you could have multiple
records, each with their own quantity. I suspect (but can't tell from the
description) that one customer can have multiple "orders"/?jobs?. If true,
this structure is more like a spreadsheet than a relational database table
.... and that is NOT a good thing in Access!

Order Status - combobox with value list

?Status of THIS Order? Apparently you don't need to track the history of
status changes on an order. (see previous note about using value lists
inside comboboxes inside forms)

ID - Autonumber


TblDepartment

Department - combobox used to store department name row source is a table
named tblDepartmentLookup

Again, why store the name if you have an ID field in the
tblDepartmentLookup?

Job_Number - textbox where users will scan in job number

What does [Job_Number] have to do with [Department]? I'm not suggesting it
has nothing to do with it, just pointing out that I don't know enough about
your specific situation to be able to answer this myself.

Customer - textbox that will hopefully auto-populate customer name

Here's the point of my previous response ... why show customer name here?
Why include any information about customer here?

Serial_Number - textbox where serial number will be entered

Assembly_Number - textbox that will auto-populate assembly number

RoHS - textbox that will auto-populate yes or no

If this field is determined by the value(s) in other field(s), you don't
need to store it ("... will autopopulate ...").

Inspector_ID - Textbox

Why not a combobox/lookup?

Previous_Operator - Textbox

Why not a combobox/lookup?

Job_Function - combo box with value list

(see value list comment above)

Process - combo box with value list

(see value list comment above)

 

[DataDiva via AccessMonster.com]

Jeff

Thank you for your insight, I was able to figure things out by following your
suggestions!

Courtney

 

[Jeff Boyce]

Courtney

Thanks for posting back.

Other folks in the future may run into the same issues you faced.

Consider posting how you "figured things out" so other folks could benefit
from your struggle <g>!

Regards

Jeff Boyce
Microsoft Office/Access MVP