Subform not saving data...WHY?

[CMooreJr]

Hey all! OK, I have been tasked to get something to work TODAY so the company can show it to the President of the company tomorrow morning...nothing like pressure huh? Here's the scenerio...

I have a form and a subform. The form contains Retailer info (address and such), Vendor info, Week Beginning, Week Ending, and a primary key.

The subform has the following fields, Retailer, Vendor, MSRP, Net Cost, Available, etc...They are linked by Retailer and Vendor, which works great.

THE PROBLEM---

Say the user puts in the following data:
FORM:
Retailer: CompUSA
Vendor: Belkin
Week Beginning: 01/01/2004
Week Ending: 01/08/2004

Subform:
(Retailer and Vendor are hidden and autopopulate)
MSRP: Net Cost: Available: etc....
$50.00 $25.00 250

This data may be EXACTLY the same for Week2 (say 1/8/2004 to 1/15/2004) or just the availablilty may change. There could be up to 25-30 records in the subform and the user DOES NOT want to retype this data every week. So I set up a macro which I thought would work and attached it to a button labeled "Copy data to new record" Here is the macro...

GotoControl cboCompany (First tab on the form)
RunCommand SelectRecord (Selects the entire Rcd)
RunCommand Copy (Copies the entire record)
GotoRecord New (New record on Form)
GotoControl cboCompany (Sets the focus again)
RunCommand PasteAppend

Now, all data copies over to a new record at the form level perfectly, it creates a new record and puts all the data where I want it, but it never writes back to the table! What am I missing??? Hopefully it is something very simple, I just cant figure it out!!

HELP! Thanks!

 

[CMooreJr]

Sorry....I failed to mention that if the data is TYPED into the subform, it does populate the table like it should. Only if the macro is ran does the subform data not make it to the table... so the problem must reside in the macro...

Thanks!

 

[Rolliee]

Many problems 'reside in macros!!!!!' why not do it with vb?

 

[CMooreJr]

I stink at VB! Can you help???

 

[Rolliee]

Sure,

Under the copy data to new record command button put the following code.

sub cmdCopyIT_click()

DIM ar(10) as variant, i as integer


for i = 1 to 9

ar(i) = me.fields(i)

next i

DoCmd.GoToRecord , , acNewRec

for i = 1 to 9

me.fields(i) = ar(i)

next i

exit sub

'This assumes that 0 is an autonumber and you do not want to mess with it. It assumes that you have 9 fields on the form. Change what you wish.

Rollie

 

[CMooreJr]

cool...OK, I have put that into the on click() command...I get a compile error on this line however when I try to run it...

ar(i) = Me.Fields(i)

it highlights the .Fields(i)

should I change that to the field names to mbe copied?

By the way, this may help you figure out why the data is not moving over from the subform...there are actually 2 tables. The table for the main form "TblInventory" and the subform table, "TblInventorySubform". The record source for the entire form is "TblInventory" which may be why it's not saving the subform data. I tried changing the form record source to a query with the tables linked, but it will not allow me to add records if I do that...

Thanks for everything!

 

[Rolliee]

Look at the query associated with the form's data source and you will see the order of the fields. A field can be addressed in a number of ways:

me![FieldName]

me("FieldName&quot

or

me.fields(i) where i is the position base zero (first is zero - etc)

YOu can directly address a table (which underlies the form)

by

set rs = currentdb.openrecordset("tableName&quot

You can address a foreign database by setting a database variable = db and

set rs = db.openrecordset("Table&quot

VB is so powerful you dare not learn it.

Rollie