Creating a Subform record from data in the main form

G

google

HI.

Can anyone shed any light.

I would like to add a function button to a form that when pressed
create a complete record in a subform. The data is to come from the
main form.

This is for a shipping label database.
The main form has all the relevant customer information and reference
numbers.
The sub form is to contain the data for the shipping/packing labels. I
need to be able to create different versions of labels however all
based on the main form data.

I would like to see a button called (Create Label) once pressed all the
data from the main form is transfered in to a new record on the subform
where it can then be either left or ammended.

And if i need to create another label i would just press (Create Label)
again.

I would appreciate any help that can be given.

Regards
 
J

Jeff L

What is the Record Source of the subform? If it is a table, then all
you would need to add the data to the table Something like this:

Dim rst as Object
Set rst = CurrentDb.OpenRecordset("SubformTableName")

With rst
..Addnew
!ID = Me.Id
!Address = Me.AddressField
!City = Me.City
And so forth
..Update
End With

Me.SubformName.Requery

Hope that helps!
 
G

google

HI Jeff

Thanks for the info, the subform is a table called "Items" and the Main
Form is also from a table called "Jobs" and all the fields are
identical in both tables.

I have tried the following code but get

Run-time error'3265'
Items not found in this collection.

My Code for the button is:

Private Sub Command55_Enter()

Dim rst as Object
Set rst = CurrentDb.OpenRecordset("Items")

With rst
.Addnew
!Job_Number = Me.Job_Number
!Source_Code = Me.Source_Code

.Update
End With

Me.Items_subform.Requery

End Sub

When I debug, the following line
!Job_Number = Me.Job_Number
is highlighted yellow, if i hover over the Me. variables it reports the
correct values, the problem seems to be the !job_Number and
!Source_Code variables on the subform.

I guess i am missing something simple but can not see why.

Thanks
 
J

Jeff L

Is the field name in the table Job_Number? My guess is that it's not.
If it is Job Number then you need brackets around the name, [Job
Number].
 
G

google

Hi Jeff
Thanks for the reply, I have modified the code with some changes but
now have a compile error

It debugs with .Items in blue from the last me.items.requery line
then when i click ok, i get the Private Sub_Command55_Enter() in yellow

Private Sub Command55_Enter()

Dim rst as Object
Set rst = CurrentDb.OpenRecordset("Items")

With rst
.Addnew
![Job Number] = Me.Job_Number
![Source Code] = Me.Source_Code

.Update
End With

Me.Items.Requery

End Sub

Darren
 
J

Jeff L

You must have the wrong name then. I noticed in your previous post you
had Me.Items_subform.Requery Should it be this instead?
 
G

google

hi jeff

the table is called items and the subform is called items. I renamed it
from items_subform thats why there is the change.

when i type me. as a command it then gives me a list of things but the
word items is not one of them. i can not seem to get the reguery to work
 
J

Jeff L

In design view, single click on the edge of the subform so that it's
selected. Right click and select properties. Click the Other tab.
You should see a value in the Name field. That is the name of the
Subform that you need to use.
 
J

Jeff L

Another thing I noticed, you should have this code in the On Click
event of the command button, not On Enter.


Jeff said:
You must have the wrong name then. I noticed in your previous post you
had Me.Items_subform.Requery Should it be this instead?


Hi Jeff
Thanks for the reply, I have modified the code with some changes but
now have a compile error

It debugs with .Items in blue from the last me.items.requery line
then when i click ok, i get the Private Sub_Command55_Enter() in yellow

Private Sub Command55_Enter()

Dim rst as Object
Set rst = CurrentDb.OpenRecordset("Items")

With rst
.Addnew
![Job Number] = Me.Job_Number
![Source Code] = Me.Source_Code

.Update
End With

Me.Items.Requery

End Sub

Darren
Click to expand...
Click to expand...
 
Top