simple "order entry" form help

[dcorleto]

I three tables.

1. Projects (code is unique field)
2. Vendors (have a a list in there and need to add as necessary)
3. Resource (a way to categorize what the vendor does - have some things in there, and need to add as necessary)

I want to create a form that selects a code from the projects table as a drop down menu
the do the same for the vendor and the resource. Then I want to have another field called amount that I can enter in a $$ value for the vendor's invoice. Then have all that saved in either a form or query that would look like this:

Code Vendor Resource Amt
12345 ABC Co. widgets $400

I don't know why I am having such a roadblock with how to set this up, as I have a complete working database that uses these tables already, but I can't seem to get a grasp on how this should be setup properly. Please help

 

[fneily]

tblProject, tblVendor, tblResource, tblTransaction
tblTransaction looks like this:
TransactID (Primary Key)
ProjectID
VendorID
ResourceID
Amount

ProjectID, VendorID, ResourceID are foreign keys.

You would then create a form based on tblTransaction with just Amount field from it. You would then create three combos using the wizard and storing the ProjectID, VendorID and ResouceID in the tblTransaction table fields.

Please read up on Normalization.

 

[dcorleto]

i did as you instructed and the form works great! Thank you so much. What I need to do though is be able to either select from one of the dropdowns for the vendor and if it's not listed, can I add one in the form, and then would it append a record to the tblVendor table? That would make it perfect if I could add (append) records in the Vendor table while using the data entry form.

 

[fneily]

For your Vendor dropdown, go to design view and bring up the property sheet. Check the Rowsource, it should look something like this:
SELECT DISTINCT [VendorID] FROM tblVendor ORDER BY [VendorID];
Also check the Option Limit To List. Set it to NO.
Just making sure everything is set up all right.

Then click the Event Tab of the property sheet. Click in the box next to AfterUpdate. Click the button with the three dots and select code builder. Place the following code there:

Private Sub comboboxname_AfterUpdate()
Dim R As DAO.Recordset, RS As DAO.Recordset
Dim SQLText
Dim UserSelection
Set R = CurrentDb.OpenRecordset("Select * From [tblVendor] Where [VendorID] = " & Me![VendorComboBoxName]
If R.RecordCount = 0 Then
UserSelection = MsgBox("Vendor Does Not Exit" & Chr(10) & "Add To Table?", vbYesNo)
Select Case UserSelection
Case 6
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblVendor", dbOpenDynaset)
RS.AddNew
RS![VendorID] = Forms![YoutFormName]![VendorComboBoxName]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
Case 7
End Select
Me![VendorComboBoxName] = Null
Else
SQLText = "Select * From [tblVendor] Where [VendorID] = " & Me![VendorComboboxName]
Forms![YourFormName].RecordSource = SQLText
Me![VendorComboBoxName] = Null
End If
End Sub

Change names where appropriate.