Form recommendations/critiques? ComboBox issues?

[TerraEarth]

Hello everyone. I picked up Access for fun and have been toying around with a few projects, but just couldn't find the best way to approach it. Here's the problem:

Background:

I have a database whose purpose is to record down information about packages; details about the package and about the people who drop off/receive the package.

I created two tables, one for the package details (I am only dealing with single-package cases for now, when I feel a bit more confident I will tackle a multiple-package problem) and another for the people's information (both receiver and sender). I grouped the two categories of receiver/sender in one table because a sender can be a receiver, and a receiver can be a sender.

The package table has ID fields, one for the receiver and another for the sender. The "customers" table comprising of rec/senders are linked to the package table via a one to many relationship through their respective ID fields in the package table.

Question:
When building a form for inputting data about packages/customers, how would you suggest I construct it such that I can have two combo boxes (one for sender, one for receiver) where you can either look up a customer BY NAME or manually type in a new name to add a new customer to the CUSTOMERs Table. After selecting a customer or inputting in a new one via the combo box it would either populate the relevant customer data on the form or it would allow you to input in data for the new customer in those same (empty) fields. The package fields will be located at the bottom of the form where the user can input data about the package which is connected to the two listed customers.

Any suggestions on how I should go about achieving this or if there's a better way of doing it? How do I go about setting up the combo box (or some similar equivalent) to have it be able to both populate customer data on the form or enter new customer data?



Here's a sketch of what I'm envisioning in my head:

[June7]

If you want to be able to create new customer record 'on-the-fly' during data entry, use the NotInList event of combobox. Common topic. Here is one tutorial: http://www.blueclaw-db.com/access_no...ed_example.htm

[orange]

You may find this Not in List link helpful.

Can you show us your tables and relationships?

I'm guessing something along this setup.

[TerraEarth]

Thanks for the replies, I'll check out those links!

And yes orange, that is exactly how I have my database setup (I'm not currently at home at the moment).

My main questions for you would be

1. Would there be a way to source both IDs from the same table somehow? Otherwise if Bob and Jill both send and receive packages on a day-to-day basis, (which is how most people operate in the real world) then I would have to enter them both twice which is essentially twice the data-entry work. Or is this the only way to feasibly operate this form?

2. It is possible to lock a subform to the main form? If I have my "customer" subform on the "package info" main form, if I set up 'customerName' as a combo box, if IDs have been entered for receiver/sender into the package info form, then the subform will display the data for the customers scrolling through package form entries
There will be a navigation panel at the bottom of the subform (left and right arrows) that allows me to strafe through entries in "Customers" when actually I want this subform to be locked to display only the customer for that particular package, i.e. no strafing.

[orange]

Interesting word strafing

from Google search:
strafe


gerund or present participle: strafing
attack repeatedly with bombs or machine-gun fire from low-flying aircraft.

1. Would there be a way to source both IDs from the same table somehow? Otherwise if Bob and Jill both send and receive packages on a day-to-day basis, (which is how most people operate in the real world) then I would have to enter them both twice which is essentially twice the data-entry work. Or is this the only way to feasibly operate this form?

I would suggest using the People table as the Rowsource of the Combobox for Sender.
You would also use the People table as the Rowsource of the Combobox for Receiver --just ensure the Receiver can not be the Sender for the same package. (use a where clause on the rowsource sql). For data entry (sender), just select the Person's name from the dropdown, the PersonID would populate SenderId in the Package table. Similar set up with Receiver combo to populate ReceiverID. The combo will display name, but record ID.

Not sure I'm following point 2.

It seems(untested) you would have Customer (sender) on mainform. Once you select sender from combo, then you enter info re the package and select the receiver from receiver combo. If there are more packages, you use the subform to identify same or new receiver, and that package's details.
If you have a new Sender, you move to new record on main form.

[TerraEarth]

Strafing is a term I picked up from video games, it means to move side to side along a horizontal axis. So basically I am referring to locking the side-to-side movement functionality of the subform (i.e. to lock it down).

To clarify point 2 a bit more, I'll give out a example.

Let's say there are package forms P1, P2, P3, ... PN (where N stands for newest/new)
There are customer subforms C1, C2, C3, ... CN (N standing for newest/new)

Subform i is attached to main form i, so Ci is linked to Pi.

When I have Pi shown on my form screen, Ci is also displayed. However, I am able to "strafe" between customer records while still being on Pi, so I could be on P3, but look at C2, C1, C4, etc.

What I would like to do is to have some kind of functionality/button/macro/whatever you might recommend in order to lock a customer, CN to PN once a new PN has been processed. If someone wants to go back and edit the CN of PN, they would have to go through a error message box that perhaps says something like: Are you sure you want to change the organization for package ID# XXXXXX ? And Click "Yes" before doing so. Or another possibility is I could lock edits from the user entirely and set it up so that only the administrator of the database can go back and edit past files.

[June7]

There is an AllowEdits property of form. If set to No then existing records can be viewed but not edited. Do users have any requirement to view existing records? A form set with DataEntry to Yes will only allow new records entered and not even display existing records.

However, all that is moot if users have direct access to tables and queries.

[TerraEarth]

Do users have any requirement to view existing records? A form set with DataEntry to Yes will only allow new records entered and not even display existing records.

However, all that is moot if users have direct access to tables and queries.

I want them to be able to view the available choices for customers/people when filling in the combo boxes, but I don't want them to be able to go back and change older forms without some kind of verification control built in (warning messages, admin access only, etc.).


I've run into another issue, when I update the Customers name combobox in the subform, I wanted the Package mainform to be automatically updated with the customerIDs. So I wrote a requery in the AfterUpdate field of the subform for the customerID field.

Private Sub ReceiverName_AfterUpdate()
Forms![fPackages].Requery
End Sub


However when I update the field for ReceiverName on my subform it gives me the error message:
The expression After Update you entered as the event property setting produced the following error: Object or class does not support the set of events.
Not sure what I might be doing wrong here. I set the rowsource and controlsource are both set to the the ReceiverName field in the Customers table.

[orange]

I mocked up this as a skeleton form based on the tables:
People

PersonId	PersonName	OtherPersonInfo
1	Jim
2	John
3	Sam
4	Mary
5	Sue
6	Jarhu
7	Cyan

Package table




Form and subform



There is no detail, and I don't know your requirements.
In this form/subform the Sender is on the Form, the Receiver and Package details are on the subform.
When you select a Sender, that person is removed from the list that values the Receiver combo.
You select the Receiver and populate the Package info(I have minimized Dimension etc), then Click the Button.
This adds a Package record to the Package table. If you stay on the same Mainform record, you can continue to add Package records.

This is the table set up


You use the People table to value the Sender combo.
You also use the People table to value the Receiver combo, but you remove the Sender from the Receiver list.
Here is the SQL involved to populate the Receiver combo..

SELECT People.PersonId, People.PersonName
FROM People
WHERE (((People.PersonId)<>[Forms]![frmMain]![CboSender]))
ORDER BY People.PersonName;

Here is the code behind the button click event.

Private Sub cmdCreate_Click()
    Dim sql As String
10  On Error GoTo cmdCreate_Click_Error

20  sql = "Insert into Package (pkgTimeStamp,SenderId,ReceiverId,PkgWeight_oz,PkgDimension_inches,PkgComment,OtherInfoForThisPkg) " _
        & " Values(#" & Now & "# ," & Me.Parent.cboSender & " ," & Me.cboReceiver & " ,'" & txtWeight _
        & "' ,'" & txtDim & "','" & txtNotes & "' ,'" & txtOtherInfo & "')"
30  Debug.Print sql
40  CurrentDb.Execute sql, dbFailOnError

50  On Error GoTo 0
cmdCreate_Click_Exit:
60  Exit Sub

cmdCreate_Click_Error:

70  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdCreate_Click of VBA Document Form_sfrmPackage"
80  GoTo cmdCreate_Click_Exit
End Sub

And a typical Package record looks like:

Insert into Package (pkgTimeStamp,SenderId,ReceiverId,PkgWeight_oz,PkgDimension_inches,PkgComment,OtherInfoForThisPkg)  Values(#07-Mar-18 4:53:22 PM# ,1 ,4 ,'132' ,'12','This is  sample note for Mary' ,'This is other info for Mary and bla bla bal…')

Hope it's helpful.

[TerraEarth]

Quite the monster of a response! I will go over it in detail and mull over it and try to process as much as I can. It is very helpful, thank you. I'll touch back if an issue pops up but this looks to be what I was looking for.

[orange]

I'm attaching the skeleton database that may help with testing/developing.
Good luck.