pass a value from a main form to a subform

[tragaz]

Hi There,

I'm a beginer in Access and VB.
In my Database, I've got a form with a subform.
the subform is a query from a table different than the table of the main form. those tables don't have a link yet.

the main form is being use to create a new entry/ a new row in the table.

What I want to do is use the primery key from the main form and copy the value in a field in the subform. In the subform, I first need to select some rows. The value has to be copied when I select the item, there is a field beside where the value should be.

I try different thing but I haven't find a solution that work.
one of the solution, the most logical for me, is to add an event 'on click' when I choose which element I want in the list. I used the expression builder but it doesn't work. one of the expression was something like this:

 =([Transaction]=[Forms]![Kit Transactions Form]![ID])

This is not working, I'm not sure why, and I haven't find any function or expression that could do the trick.

This will be used later one to link the two tables and create reports.

I hope anyone understood what I want to do, it is quite complicated to describe everything in words. I attach a print screen of the form and subform.

Any sugestion is welcome.

Thanks in advance

[tragaz]

Hi again,

I just try to put this code in the 'on click' event

Private Sub Used_Click()
    Forms![Kit Transactions Form]![Courier Bag Inventory Query subform].Form!Transaction.Value = Forms![Kit Transactions Form]!ID.Value
End Sub

It still doesn't work

Thanks for any help

[MMcCarthy]

You actually have to take this back to the relationship between the two tables.

If we call Table1 the name of the table behind the main form and Table2 the name of the table behind the subform. Now Table1 has an ID field called Table1_ID and Table2 has an ID field called Table2_ID and a foreign key called Table1_ID.

Now it's important that Table2_ID has a data type of autonumber. There are other ways of doing it but this is the simplest. Set the default value of of the Table1_ID field in Table2 to ...

=Forms!MainFormName!Table1_ID

Now set the parent child relationship between the main form and the subform to be based on Table1_ID.

You will find that as you start to add value to any of the other fields in the subform, that Table2_ID will generate automatically as it is an autonumber and the value in Table1_ID in the subform will reflect the value of Table1_ID in the main form.

I hope I've explained this clearly. Let me know if you can't follow.

Mary

[tragaz]

Thanks for this,

the thing is that in Table2, there are already some existing field, I don't want o create new ones, at least not in this case.

In fact it is in Table1 that a new row is created by filing up the main form.
I actually want to copy the value of the Table1 primery key newly created to some field in Table2, by selecting in the subform which field to copy.

You're solution is sort of work since in the subform, if i wanted to create a new row, then the Primery key would be copied, but it does't copy in field that I'm selecteing afterthat...

Hope it is clear.
Philippe

[MMcCarthy]

@tragaz
I don't understand your reply. I think it's best if you use the table names and field names you are actually using so I can better follow what you are saying.
Also please give the metadata of the tables so I can see the existing structure.

Here is an example of how to post table MetaData :
Table Name=tblBooking s

 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[tragaz]

Hi msquared

Sorry for the confusion, I though that using the name that you used in your example would be easier for you to follow.

Ok so I've got 2 tables:
Table Name = Kit Transactions

ID; AutoNumber; Pk
Kit Barcode; Text; Fk
Courier Bags; Number
Courier Bag Barcodes; Text
Cotton Rolls; Number
Vials; Number
Forceps; Number
Dispatched; Date/Time
Client; Number; Fk
Received; Date/Time
Chilly Bag Returned;T ext
Courier Bags Returned; Number
Cotton Rolls Returned; Number
Vials Returned; Number
Forceps Returned; Number
Received By; Number; Fk
Invoiced; Date/Time
Notes; Text

Table name: Courier Bag Inventory

ID; AutoNumber; Pk
Track n Trace number; Text
Used; Yes/No
Transaction; Number; Fk

Alright, so my main form is based on the table Kit Transactions
the subform is Courier Bag Inventory based on a query that shows only the data where Used='No'
In the subform, Used is represented by a checkbox.

What I want to do is when the user click on the check box, the value of Kit Transactions.ID is copied in the Courier Bag Inventory.Trans action

Also, the main form is used to create a new data in the table Kit Transactions and update data in other tables links to it. One of the update that has to be done is copying the value of the new ID into the selected items of the Courier Bag Inventory

Hope you understand

[NeoPa]

Private Sub Used_Click()
    Forms![Kit Transactions Form]![Courier Bag Inventory Query subform].Form!Transaction.Value = Forms![Kit Transactions Form]!ID.Value
End Sub

Taking a step back for a second...
This appears to me to be workable. It matches the format described in Referring to Items on a Sub-Form.

I suspect that one of these forms (main or sub) actually contains the control Used. This form can be referred to more simply as Me.

Can you say exactly what is failing here? I would certainly consider progressing on this front if possible, as it is more precisely related to your problem.

[tragaz]

Hi Neopa

the control Used is in the subform. I know that I can refer to the subform as Me but I wanted to be sure of what I was writing.
but just to be sure, if I want to use Me, should it be like this:

Private Sub Used_Click() 
     Forms![Kit Transactions Form]!Me.Form!Transaction.Value = Forms![Kit Transactions Form]!ID.Value 
End Sub 

Or like that:

Private Sub Used_Click() 
     Me.Transaction.Value = Forms![Kit Transactions Form]!ID.Value 
End Sub 

I'm not sure what is going wrong because when click on the control Used it appear that the value hasn't been copied. well at least the value is not showing in the field Transaction as well as in the Table Courier Bag Inventory where this field is... Any idea?

I also tried to add Me.Transaction. Requery after the precedent code, thinking that it might actually refresh the value of Transaction but it's not. the code looked like this:

Private Sub Used_Click() 
     Forms![Kit Transactions Form]![Courier Bag Inventory Query subform].Form!Transaction.Value = Forms![Kit Transactions Form]!ID.Value
     Forms![Kit Transactions Form]![Courier Bag Inventory Query subform].Form!Transaction.Requery 
End Sub 

With Me:
(sorry for the repition I'm just not sure if I'm using Me correctly or not)

Private Sub Used_Click() 
     Me.Transaction.Value = Forms![Kit Transactions Form]!ID.Value
     Me.Transaction.Requery 
End Sub 

Thanks in advance for any help

[NeoPa]

tragaz: should it be like this:

Private Sub Used_Click() 
     Forms![Kit Transactions Form]!Me.Form!Transaction.Value = Forms![Kit Transactions Form]!ID.Value 
End Sub 

Or like that:

Private Sub Used_Click() 
     Me.Transaction.Value = Forms![Kit Transactions Form]!ID.Value 
End Sub 

.Value is the default property of most controls so is generally redundant.

Private Sub Used_Click()
     Me.Transaction = Forms![Kit Transactions Form].ID
End Sub

You could also say :

Me.Transaction = Me.Parent.ID

This works better if you ever need the subform to be contained on any other form than [Kit Transactions Form].

tragaz: I also tried to add Me.Transaction. Requery after the precedent code, thinking that it might actually refresh the value of Transaction but it's not.

The format would be :

Call Me.Requery

but it should not be necessary in this case.

[tragaz]

Neopa,

I wrote the command as you said, for the correct way:

Private Sub Used_Click() 
     Me.Transaction = Forms![Kit Transactions Form].ID
End Sub 

Unfortunately, it doesn't work. When I click on the Control Used, the value is not copied, well at least I don't see the value in the Transaction control and in the field in the table.

Any idea how I could do that?