Design help

[DrillMonkey]

Hi, I need help in form design. The form will be multi-user (2) people and needs to have a lot of room for data input. I was thinking of using a main form with just a few controls i.e. “Date” “User” “Recordid” “Ship to” and “TotalParcels” Then have a sub form handle the data entry “Description” “PurchaseOrder” “Requisition” “StoreOrder” “TicketNumber” “Memo” and “Parcels” the user would fill in the main form first then jump into the sub form and fill in the rest.

**********************************************************
sub form



Description PurchaseOrder Requistion StoresOrder

Buyouts 864r12012 86402222 64s2258
Stores 864t00122 86402369 64s9966
Repair

***********************************************************
Is this a soild approach? I guess my question is will this create multiple records or just one? I would like it to create just one record so i could print with this type of code

    Private Sub cmdPrint_Click()
    Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ID] = " & Me.[ID]
        DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
    End If
End Sub

 

[fneily]

You seem to have a common one-to-many relationship so a form/subform will do. This will allow for either one or many records to be inputted in the subform.
To see a single user and their records, you'd simply create a query to pick off the records and send it through the report process.
You don't show your table structures and the linking field.
Also, you have a "totalparcels" field in the main table. Does this relate to the "parcels" field? If it does, you do not hold totals in any table. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[DrillMonkey]

Hi fneily,

Thank you for responding. Here are the (4) tables


tblWarehouse tblSchool tblDriver tblDiscription

RecordID Schools Driver Department
Date (combo box) (combo box) (combo box)
TotalParcels
Driver
School
Signature
Description
PurchaseOrder
Requisition
StoreOrder
Ticket
MemoNumber
Parcels

I dont want to store the TotalParcels in the table but i do want to show the sum of all "Parcels" on the main form. So I can delete that if needed. Also I do have a double "driver" reffrence not sure why.The MemoNumber is a number that the user might input into the data i.e. like a purchase order number, All fields are set to text except parcels,date, and recordid..I don't have a linking record any suggestions?

 

[fneily]

Is Parcels a numeric? I'll assume so.
MemoNumber is like PurchaseOrderNumber? No. You already have a PurchaseOrderNumber field. So I guess it means something else.
So, you'll have 4 tables:
tblSchool with primary key schoolID and school info fields.
tblDriver with primary key driverID and driver info fields.
tblDepartment with primary key DepartID and Description.
tblWarehouse will be a "junction" table. It connects the above tables and any COMMON data. So it would look like:

WareID SchoolID DriverID DepartID Date PurchaseOrderNo
(fields cont.) Requistion StoresOrder Ticket MemoNo Parcels

WareID is your RecordID. Would Ticket be the same as the WareID?

The first three tables can be prepopulated.
You'd create a data entry form for the tblWarehouse. You'd have comboboxes for SchoolID, DriverID, and DepartID. These would be bound to the fields in tblWarehouse. The other fields would then be filled in by the user.
Date could default to "todays" date or you could have a popup calendar.

In your first post you mentioned a ShipTo field. I guess that's school.

Not sure what TotalParcels is? Parcels remaining? Parcels that were shipped for that day? etc.

For a popup calendar, search the Access forums. There's a few posts about it.

 

[DrillMonkey]

This was the information I needed; I am reading the article in the link. I will redo my tables as you suggest. Yes parcels are a numeric. Our department has parcels delivered to us from other departments and there is paper work that has a unique number we call a memo number. The "StoreOrder and ticket" are similar to purchase order and Requisition. “Ship to” is the schools yes I miss spoke. As for totalparcels I wanted to sum all parcels and show the value in that control. I see now that I made an error with that also. You have open the door just enough for me to take a peek.

Thanks for the help
Richard

 

[DrillMonkey]

fneily,

I have a question about my one-to-many relationship in my tblWarehouse do I keep my old "RecordID" and replace it with "WareID" and are WareID SchoolID DriverID DepartID all auto number or text..thanks