Data entry form with 2 'lookup fields.

[Zombai]

I'm trying to create a data entry form, which puts information into a specific table, but has 2 fields that looks up information from a different table.



I want the 'Account Name' and the 'Account Number' to be found from the 'Accounts' table, the 'Customer Number' field generates an AutoNumber, and the other fields will be entered manually.
The save button will, obviously, save the record.
The Exit button will close the form.
The Clear Fields button will erase all data from the form (i need help with this step too)



The idea is that there are multiple Customer's linked to a single Account, via the Account Number.


You select the account you want to associate the customer with, then fill in the details and save it.
Saving it will store all the information in the 'Customers' table.

I can successfully select an account name, however it will not populate the Account Number field when selected. This causes the 'Customer' to be related to no account from the 'Account' table.

In the below image, you can see customer number 1, i manually input to and associated the account number (this worked, and was recalled for other test forms).
Customer 6 was saved using the form in the above image.


I appreciate your help

[June7]

Not understanding the issue. You select account with combobox? Include the account number as first column (can be hidden) of the combobox and set that column as the BoundColumn. The AccountNumber will save. Why duplicate the AccountName into Customers table?

[Zombai]

Not understanding the issue. You select account with combobox? Include the account number as first column (can be hidden) of the combobox and set that column as the BoundColumn. The AccountNumber will save. Why duplicate the AccountName into Customers table?

Thanks for the quick response.
I have already done as you advised.

If the form is NOT set to 'Data Entry' then it populates the rest of the fields with an existing record (such as first name, last name, phone number etc.), which i do not want it to do.
If the form IS set to 'Data Entry' then it doesn't populate the Account Number field.

I want the form to be blank. You then select the account name, it populates the account number field (to visually verify it has selected an account to save against), input customer details, then save the form which creates a new record in the Customers table

The 'Account Name' field is in the Customers table to make it easier if i need to manually look at the table.

[June7]

Saving the Account Name into Customers violates relational database principles and requires code to accomplish. Use queries to view related tables.

[Zombai]

Saving the Account Name into Customers violates relational database principles and requires code to accomplish. Use queries to view related tables.

Totally irrelevant to the question that i asked.
But i saw the error in having multiple "account name' fields in different tables. I've not removed all duplicate fields in tables (except Account Number).

I managed to fix my issue by creating a form for Accounts, with a subform for Customers. The Customers subform is a data entry form and it is all working perfectly.

Can you explain how to create a button to clear information that is already entered in the subform?
For example, you fill out the subform, then realise you don't want to add the information anymore. Instead of closing the form without saving, you just clear all data that is already entered, but not saved?

[Zombai]

For anyone reading this thread in the future, i found an answer to creating a 'clear' button to reset a data entry form.

Create a button with the Control Wizard turned off - find the 'On Click' event, click the button with the 3 little dots and open the Code Builder - Add 'Me.Undo' between the two lines of code already there (like the image below)

Private Sub Command15_Click()
Me.Undo
End Sub

That's it.