Single form submit data to different tables ? (1 Viewer)

cwager990 · Jan 19, 2019

Hi Guys,

Lets say for example I have a form that the user fills in with details such as Name, Address and so on. That is then stored in table.

What I would like to be able to do is have it stored in on of two tables based on which submit button is pressed is there anyway to do this in Access ?

Thanks

isladogs · Jan 19, 2019

Yes its certainly possible but only if your form is unbound.
Each button click would run a different SQL append statement or append query.

If the form was bound then the save would already be done automatically to the bound table

However the bigger question is why you would have 2 tables with the same (or similar) structures. Combine the data into one table with an identifying field added to distinguish the data as necessary.

Doing so means your form can be bound and save you a lot of work!

cwager990 · Jan 19, 2019

The Ideal was for a Form that doubles up as the Quote and Order form, I had considered a Yes/No Field to Identity it as a Quote the issue being that we need to keep a copy of all the quote data after it converts to a sale, as well as the sale data which makes it more complex.

isladogs · Jan 19, 2019

That's not necessarily a problem as the quote and sale would just be two separate records identified by S or Q (or a Yes/No field as you suggest)

The same idea is used in accounting apps where income and outgoings are both done using an 'Amount' field with one being + and the other -

However, if you really want separate tables, then I suggest a main form with 2 bound subforms. One for quotes and the other for sales. You could use a tab control on the main form with 2 pages to hold each of the subforms.
Alternatively just have one subform visible at a time and just use a checkbox or similar to choose which subform you want.

However you do it, using BOUND forms is much less work than UNBOUND ones.

cwager990 · Jan 19, 2019

Agreed, Tab Control, is the way to go, I think.

Is there a way to dynamically move all the content in the quote tab for a particular record to the sales tab when I need to produce a sale from the quote.

Also I wanted to lookup a customer from a different table and import them into the form I am assuming this is not possible using a bound form ?

isladogs · Jan 19, 2019

Yes - an append query based on the current record.
That would work whether you use one table or two
Or a better method would be to just reference the record from the original quote in the sale and avoid unnecessary duplication of data

The customer could be selected using a combo or listbox bound to the other table. The selected value is then used on your form as needed.
That's a standard method of working in Access

cwager990 · Jan 19, 2019

I think I need an access manual ha

cwager990 · Jan 19, 2019

The customer could be selected using a combo or listbox bound to the other table. The selected value is then used on your form as needed.
That's a standard method of working in Access

Is there a way to do that with a text based search ?, there are too many customers to scroll through a whole list.

Gasman · Jan 19, 2019

Could you not create a record with a status of Quote, and if accepted then change the status to Order.?

isladogs · Jan 19, 2019

cwager990 said:
Is there a way to do that with a text based search ?, there are too many customers to scroll through a whole list.

There are several methods of filtering the list. For example:
a) use a find as you type combo- see http://allenbrowne.com/AppFindAsUType.html
b) use a combination of combo and listbox -see attached screenshot
The listbox shows all records starting with the entered value

Gasman said:
Could you not create a record with a status of Quote, and if accepted then change the status to Order.?

The OP says he has to keep both separately

cwager990 · Jan 19, 2019

]b) use a combination of combo and listbox -see attached screenshot
The listbox shows all records starting with the entered value
[/QUOTE

I like this solution I am fairly new to access, how do I do this ?. I want to do it almost like your screenshot.

Thanks

isladogs · Jan 19, 2019

Its easier to show you than to explain.
I can provide a demo version for you but I'm busy for the next few hours

cwager990 · Jan 19, 2019

isladogs said:
Its easier to show you than to explain.
I can provide a demo version for you but I'm busy for the next few hours

That would be great, thank you so much.

isladogs · Jan 19, 2019

Attached is a similar example that I already had available.
Hope it makes sense to you

Let me know if you need anything explained

cwager990 · Jan 19, 2019

isladogs said:
Attached is a similar example that I already had available.
Hope it makes sense to you

Let me know if you need anything explained

I really do not get it, I will assemble another version of my file with generic data, and send it you along with an explanation of what I want to do.

If that is okay.

isladogs · Jan 19, 2019

I have visitors this weekend so can't promise to look at your database for a few days.
However someone else may do so first.

If there are more specific questions about my example app, I may be able to answer those fairly quickly

Single form submit data to different tables ? (1 Viewer)

cwager990

Registered User.

isladogs

MVP / VIP

cwager990

Registered User.

isladogs

MVP / VIP

cwager990

Registered User.

isladogs

MVP / VIP

cwager990

Registered User.

cwager990

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Attachments

cwager990

Registered User.

isladogs

MVP / VIP

cwager990

Registered User.

isladogs

MVP / VIP

Attachments

cwager990

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread