By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

How do I transfer figures from an unbound form to two (related) tables?

stonward
100+
P: 145
Hi Guys,

I have an unbound form which acts as a fast quotation form. The form opens showing a product (tyres) selected from the previous form.
The quote form allows the user to add valves and balancing (two more products) to the quote. The quantity of these is usually, but not always the same as the 'main' product (tyre), and equally the price is variable.

Once accepted I need to transfer the figures on the quote form to my Quote and Quote Details tables. I can do this for the tyre, but how can I get the valves and balancing products also added? In short, I need to add three records to my tables on the click of a button.

I hope this is clear enough. Thanks for your time.

StonwardR
Jul 4 '12 #1

✓ answered by NeoPa

I was just looking at Allen Browne's site the other day and he has a great quote for this question - which I can't find just now :-(

Essentially though, he warns that Access is designed to be worked with, rather than around. To maintain data tables Access provides bound forms. Don't expect an easy ride when trying to swim against the current.

Share this Question
Share on Google+
4 Replies


stonward
100+
P: 145
Update:

im sorry to make this seemingly more complicated: i haven't included any code etc I have tried because I have tried a great deal! One thing I have done is to transfer all of the fields to one table. I am now trying to use sql insert statements to move the fields to the 'correct' tables. Am I even close?
This is what I tried previously; an 'addnew' statement:
Expand|Select|Wrap|Line Numbers
  1. Set quoteRST = CurrentDb.OpenRecordset("quotations", dbOpenDynaset)
  2.     With quoteRST
  3.         .AddNew
  4.             ![QuoteDate] = Now()
  5.  
  6.             ![Customer] = me.customer
  7.             ![employee]= me.employee
  8.             ![QuoteStatus] = 1
  9.         .Update
  10.     End With
  11.  
The above code works for one table, but adding a similar line for the related 'details' table doesn't work.
Jul 4 '12 #2

100+
P: 759
@stonward
"but adding a similar line for the related 'details' table doesn't work."
What means this ?
How "doesn't work" ?
What happen ?
Have you an error message or nothing is happen ?

What means "similar code" ?
Is this "similar code" exactly like this one (except the names, of course) or is not as "similar" as we can think by reading your words ?
I ask because in the related table ("details") you must write the Foreign Key (from table "quotations") too.
That means that you must "capture" the related FK after you update the "quotations" table.
So... the code don't seems to be as "similar" as you say.

Why you don't use a form (bounded to "quotations") with a sub-form (bounded to "details") ?

Cheers !
Jul 5 '12 #3

NeoPa
Expert Mod 15k+
P: 31,419
I was just looking at Allen Browne's site the other day and he has a great quote for this question - which I can't find just now :-(

Essentially though, he warns that Access is designed to be worked with, rather than around. To maintain data tables Access provides bound forms. Don't expect an easy ride when trying to swim against the current.
Jul 5 '12 #4

stonward
100+
P: 145
Thanks for your time.

I haven't explained it terribly well I think.
Naturally a bound form/subform would be the 'proper' way, but I haven't (yet) been able to make a useable form that fulfills the requirements - I'd have to explain the entire system to persuade you of that though!
I'll break it down and get back to you.
Thanks again.
StonwardR
Jul 6 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.