Error when leaving out Client ID field

[lolosuarez87]

Hi,

I have a form for rentals where I have fields for car details and for client details. I have a list box for clients so that when I enter the Client Id I automatically get all the records filled out on the form, from the clients table...

This works but the problem is that sometimes I have a new client who rents a car that is not on the database so I insert the client details manually on the rentals form without filling in the client ID as this client does not exist on the database.

The problem comes when I want to save the form it wont allow me to leave the client ID field blank... I imagine this is because of the relationship...

Could someone help please!? Thanks

 

[Uncle Gizmo]

Why not add them as a new client?

 

[lolosuarez87]

Yes, I will make an append query which will add new clients in the clients table when clicking on the save button after adding the rental details.
If I enter a Client ID in the Client ID field it requires me to have an existing client ID in the clients table. All I want to know is how to be able to leave the Client ID field blank with Access not giving me an error.

 

[Uncle Gizmo]

Comment deleted. I got the wrong end of the Stick....

 

[Uncle Gizmo]

Add a client to your client database named "New Client". You can select this as a client when you are entering a new client and not leave the client ID empty.

 

[lolosuarez87]

Ok.. so I have created a new field with two selections "New Customer" and "Existing Customer". If one selects existing customer the user enters a value in customer_id field and all the info appears on the screen with dlookup. When a user enters a new customer the customer_id field automatically displays the max value of the client ID table + 1 to it.. I added an IF function when saving which said if Customer= "New Customer" save the customer details on customer table and this works fine. The only thing is when I save the whole form it says it won't allow me because of duplicate data of customer_ID!
Why is this???

Customer_ID is primary key in the customers table with auto number, and Customer_ID is in the rentals table but not a primary key!!

Why does it display this error??
Any suggestions??
Thanks!!

 

[Uncle Gizmo]

What happened to your list box?

 

[lolosuarez87]

I cant see how that would help.. I created a client list box too but when i create a new client... Say i have last client id on client table 35 and i add a new client and save... The client is created with client id 36... But on the form in the list box, the client id 36 does not appear... It only appears when you close the form and reopen it.... :banghead::banghead::banghead:

 

[Uncle Gizmo]

You've lost me.....

 

[DreamingSpires]

Why not have a button on your Rental form 'Add New Client' that opens a pop-up modal form bound to your client list table with fields for the client details you need to fill in? Have that form open in 'add new record' model. When you save that form, it'll close and create the new client. If you have a refresh event for your Rental form attached to the 'Add New Client' form's OnClose event, then your new client will be available in your Rental form.

Pinch of salt, because I'm an Access newbie!

 

[MSAccessRookie]

I cant see how that would help.. I created a client list box too but when i create a new client... Say i have last client id on client table 35 and i add a new client and save... The client is created with client id 36... But on the form in the list box, the client id 36 does not appear... It only appears when you close the form and reopen it.... :banghead::banghead::banghead:

If you change the values returned by the Query or Table controlling a Dropdown box of any kind, VBA Code like me.ComboboxName.Refresh should update the list without having to close and open the form.

 

[lolosuarez87]

Yes! Thats what it was.. I tried requery just before i read your post and it worked..
Wow I was about to give up!

THANKS