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=tblBookings

 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.Transaction

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?

[NeoPa]

Normally, when something as simple as this is nmot working, it indicates that the OP (Original Poster - You) has overlooked some important information. Obviously there is very little chance that I could determine what that might be, so I suggest you post a (possibly sanitised) copy of your database for me to look at. I would hope that I could determine the missing factor with all the details in front of me.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[tragaz]

Neopa,

I'd like to do that but the database is kind of confidential and I don't know if people outside of my firm are able to access it.
The database has been created before I arrive in the firm and they asked me to help resolve the problem...

Is there anyway that I can give a sample of the databse only to you?

Thanks for your understanding

[NeoPa]

That's exactly why I mentioned sanitised. I'm very careful about giving out my private details and giving my email address would not be careful.

Is there any reason you could not create a sanitised version that would be fit for public consumption? I don't see much of the data being relevant to the question at hand. I'm sure if you follow the posted instructions carefully you could provide something that works for all of us.

PS. Alternatively, it may be easier to create a whole new database with just this issue within it and attach that. Importing any relevant objects across is pretty straightforward.

[tragaz]

I clean the database so all the data inside are irevelant, they have no meaning.

I try and the problem is still here.

what you need to do when you open the database is:
first say no to the switchboard thing cause I deleted it...

everything is happening with the form Kit Transactions
in a first place you have to select a client number from the list, then you can select a kit bar code from the list too. a new ID number will be created then

What I want to do is in the subform, there is a list of items called courier bag, choose a maximum of 5 courier bag by clicking on the check box corresponding to the courier bag. And when you click, the ID newly created should be copied in the blank field called Transaction

And that is where I got my problem. If you can find a solution for that, that would be awesome.

Cheers

[NeoPa]

Good job!

I cannot download during the day, I'm afraid. I will take a look at this later on my home PC and I'll let you know anything I come up with.

[tragaz]

Great, Hope to hear from you soon

[NeoPa]

I do apologise for not getting this last night. I'm afraid I had another visit to the dentist and I had an attempted easy evening to deal with some pain issues. I'll send myself a note home to make sure I do it tonight.

[NeoPa]

Having spent the last 1/4 hour trying to follow your instructions using the form [Kit Transactions], I got thoroughly confused and found that [Kit Transactions] is simply a table (not a table format form as I'd assumed - led by your instructions).

I'm now working on the form called [Kit Transactions Form] (Don't blame me. You gave it the name). I'll post back if I manage to discover anything tonight.

[NeoPa]

I managed to follow the procedure as you indicated. I must say that I saw no problem with how the form was working. Can I just confirm that you see no [Transaction ID] (from the main form) populated into the [Transaction] control on the subform of the record just selected?

The only problem I noticed was that it allows you to deselct an item, but the [Transaction ID] is never cleared in that case.

[tragaz]

Hi Neopa,

My apologise for the incomplete name of the form. Yes it is on the form Kit Transaction From.
I confirm that I see no Transaction ID from the main form into the Transaction control of the subform. That is what I want to do.

The problem you saw is right. I just haven't done anything on it yet, I wanted in a first time to see the transaction ID populated into Transaction. Do you have any idea why it's not working?

[NeoPa]

I'm afraid it works perfectly for me.

This unfortunately indicates something outside of Access as being the issue. Maybe a setting somewhere that effects how your code is run. That's quite unusual, at least from my experience.

[tragaz]

So you can see the Transaction ID from the main form populated into Transaction control of the subform; and it is shown in the table Courier Bag Inventory too?

[tragaz]

it's weird cause it's not working for me... Could it be cause I'm on Access2007?

[NeoPa]

As a bonus though, try this :

Option Compare Database
Option Explicit
 
Private Sub Used_Click()
    With Me
        .Transaction = IIf(.Used, .Parent.ID, Null)
        If .Dirty Then .Dirty = False
    End With
End Sub

If you like it, study the differences. See what each line does. Why it's there. Etc. Particularly Option Explicit. This should always be set in all your modules (See Require Variable Declaration).

[tragaz]

ok thanks I will try that and see are the lines doing

Thanks

[NeoPa]

tragaz: So you can see the Transaction ID from the main form populated into Transaction control of the subform; and it is shown in the table Courier Bag Inventory too?

A definite confirmation of the first bit.

The second bit I can now confirm (having checked) but I'd have been extremely surprised if I'd found otherwise as it's already shown on the form.

Having said that of course, I now see your own earlier attempts with blanks in the [Transaction] field.

To answer your later question :
Yes. As far as my limited understanding goes, I see the 2007 version thing as a possibility. In as much as I don't know it couldn't have that effect. I know of no reason it would mind, but you have a problem. You are using 2007. I cannot say there is no correlation.

[tragaz]

alright,

well just try with the code you gave me and it stil doesn't work. I will look at the code you used and understand it...
I don't really know what I should do to fix this prob since we all using access2007 here...

Thanks a lot for your help though I really apreciate

[NeoPa]

I suggest trying to trace through your code (See Debugging in VBA). Just to confirm that any code is actually running. I've heard there are situations in A2007 where the code is disabled automatically. Check this isn't the case first. That is probably fixable (though not with my help as I still use A2003 and know very little of A2007).