Yes, I mean tables. Having to convert from Pdox where previous users used
"database." And same with "#". If I start over, I'll omit these. Here's
more than you want to know:
DC Technical Application: Database
Tables:
Tbl TECHNICAL_DATABASE (prefix for field names “Techâ€)
Primary Key – Tech RN Number (matches Rpt Regulated Entity No of Tbl report1)
Data entry fields to be saved and maintained in this table.
Tech Mach Status, Tech Mach Status 2nd and Tech Mach Status 3rd use combo
box Tbl DC Machine Status (for consistent data entry)
Tech User ID is combo box from Staff User ID (for consistent data entry)
Tbl report1 (prefix for field names “Rptâ€) (Lookup Table)
Primary Key – Rpt Regulated Entity No – matches Tech RN Number
Use Rpt Reg Entity Name, Rpt RE Street Address, Rpt RE City, Rpt RE State,
Rpt RE Zip, Rpt RE Contact Phone, Rpt CN Customer No, Rpt CN Customer Name,
Rpt RE Bus Type for display purposes only.
Tbl Distributors_Database (prefix for field names “Distâ€) (Lookup Table)
Primary Key- Dist ID# (matches Tech Dist ID # 1st, Tech Dist ID # 2nd and
Tech Dist ID# 3rd of Tbl TECHNICAL_DATABASE
Uses Dist Company Name and Dist Status fields for display purposes only.
Tbl DC Machine Status (combo box table for consistent data entry for fields
Tech Mach Status, Tech Mach Status 2nd and Tech Mach Status 3rd)
Primary Key – Key (1,2,3,…)
Field values: Currently in Use, Permanently out of Use, Temporarily out of
Use, Unknown
Staff User ID (combo box table for consistent data entry for field Tech User
ID)
Primary Key – none
Field Values: various staff initials
Forms:
Frm TechnicalTracking Input -- Data entry form created from all fields in
table Tbl TECHNICAL_DATABASE plus fields for view only from Tbl report1 Rpt
Reg Entity Name, Rpt RE Street Address, Rpt RE City, Rpt RE State, Rpt RE
Zip, Rpt RE Contact Phone, Rpt CN Customer No, Rpt CN Customer Name, Rpt RE
Bus Type for display purposes only.
and Tbl DISTRIBUTORS_DATABASE Dist Company Name and Dist Status for display
purposes only
Desired Deliverable:
1. Enter into Frm TechnicalTracking Input Tech RN Number for storage in
Tbl TECHNICAL_DATABASE and have Rpt Reg Entity Name, Rpt RE Street Address,
Rpt RE City, Rpt RE State, Rpt RE Zip, Rpt RE Contact Phone, Rpt CN Customer
No, Rpt CN Customer Name, Rpt RE Bus Type from Tbl report1 display in
appropriate fields on form for visual verification.
2. Enter into Frm TechnicalTracking Input new technical data fields for
storage in Tbl TECHNICAL_DATABASE.
3. Enter into Frm TechnicalTracking Input Tech Dist ID # 1st (2nd and 3rd)
for storage in Tbl TECHNICAL_DATABASE and have Dist Company Name and Dist
Status from Tbl Distributors_Database display in appropriate fields on form
for visual verification.
4. Enter into Frm TechnicalTracking Input remaining new technical data
fields for storage in Tbl TECHNICAL_DATABASE including repetitive Tech User
ID, Tech Comments Date and Tech Notes/Comments. Staff wants to be able to
append comments to the record as needed without overwriting previous comments.
Steve Schapel said:
Hutch,
Just to facilitate communication, where you mention the word Database, I
think you are referring to Tables.
Also, as another "aside", it is not a good idea to use a # as part of
the name of a field.
I would suggest you use the Column property of a combobox approach, it
is probably easiest to describe. This is mentioned in the article I
referred you to. For example, let's say your Distributor table has
DistNumber as the first field, DistributorName as the second field, and
Status as the third field. So now you have a form bound to the
Technical table. Ok, put a combobox on your form, bound to the
DistNumber field (i.e. this will be the Control Source property of the
combobox). And then set the other properties of the combobox like this...
Row Source: Distributors (the name of your Distributor reference table)
Column Count: 3
Bound Column: 1
Column Widths: 1;0;0
(if you want the drop-down list of the combobox to only show the
DistNumber, or if you want the drop-down list to aslo show the
DistributorName then something like this
Column Widths: 1;3;0
Ok, now put an unbound textbox on the form to show the distributor name.
And in the Control Source property put the equivalent of this...
=[DistNumber].[Column](1)
(This assumes that DistNumber is the name of the combobox. Note that
the numbering of the columns starts at 0, so Column(1) is the 2nd
column, i.e. the distributor name).
--
Steve Schapel, Microsoft Access MVP
Had the query working for a bit, then I lost ability to input data to be
saved. Trying to fix that, the "form view" is now blank. Design is there,
form is gone. I'll start over. Desired deliverable: I have reference table
for Facility information; a reference table for Distributor information and
have built database (Technical Database) to track technical data input by
user. Technical Database will allow input of Facility number - would then
like Facility name (+address fields) from Facility reference table to appear
on form to visually validate Facility #. Then input more technical data to
be stored in Technical Database. Another section wants Distributor
information. Want to enter Dist # and have Distributor name and status
appear (from Distributor reference) for visual validation. More data entry
to Technical Database to be entered and stored.
Facility Database related to Technical Database by Facility # entry and
storage.
Distributor Database related to Technical Database by Distributor # entry
and storage.
Thanks again for your patience.
