HELP!! Copy and paste data from subform to the main form

[yhchen]

HELP!! get subform data to the main form or Dlookup with If statement

Dear all

I have a form which contains a subform (see attached graphic)

I would like to have a bottom created that on click it will copy the student number field and student name field to the main form (as shown in the graphic)

would anyone please help to shed the light how I should go about the vba code??

 

[yhchen]

The other possible solution is to have a Dlookup with If statement for the MMU ID field. (i.e. if there is a matched data then populate the student name, if not leave it blank...)

either way as long as someone knows please help....

 

[wiklendt]

are you wanting to just retrieve the data for that student when you find them in the search? or are you wanting to add that student data to a new record of some sort?

i would encourage you to make the student field in the main form a foreign key (i.e., in your table for enquiry records, make the student field from a 'lookup wizard') make sure the field for the student FK in the enquiry records has the same name as the PK in the student table (usually this will be something like "StudentID"). this will automatically create a relationship between the student and the enquiry tables. then, in your form, add the new FK student field as a control. access will automatically make this field a combo box (a dropdown). this will make it super easy for you to just select which student you want in that record.

 

[wiklendt]

and why is it called "StudentNumber" in one area, and "MMU ID" in another? it should be the same in both spots, if i understand your form correctly...

 

[gemma-the-husky]

this isnt right

a main form/subform implies that the subform shows items already related to the main form

so how can you select something in the subform that IS NOT related to the main form record.

i think you need to explain what you are doing/trying to do more clearly

 

[yhchen]

and why is it called "StudentNumber" in one area, and "MMU ID" in another? it should be the same in both spots, if i understand your form correctly...

oh it's only the descriptions - the fields are actually both called Student_Number...

 

[yhchen]

this isnt right

a main form/subform implies that the subform shows items already related to the main form

so how can you select something in the subform that IS NOT related to the main form record.

i think you need to explain what you are doing/trying to do more clearly

the subform is simply a *sub-form* and there is no relationship built with the parent form.

What I wish to do is, the parent form will be adding a new record, but as we are getting more repeating/returning service user, I would like the function when I enter the student number, it'd search if we have their name in the database (If there is, then we don't need to re-enter the name).

So for the main form, it is to add a new record.

If I am still not making it clear enough, please let me know.... didn't sleep well last night at all as have been thinking what else I can do to achieve this....

 

[JANR]

If you add a button on your subform and in it's click event your could add something like this:

Private Sub cmdtrans_Click()
    Me.Parent!student_Number = Me.Student_Number
    Me.Parent!Student_name = Me.Student_Name
End Sub

This will transfer the values from your unbound subform to the mainform, you can also add some If_Then_else statements before you transfer.

Hope this helps.

JR

 

[Rabbie]

the subform is simply a *sub-form* and there is no relationship built with the parent form.

This just doesn't seem like good design practice. I think you might find it helpful to read up on Data Normalisation. You should not be storing the same information in more than one place. If something chaneges then it can be awkward changing the data in multiple places. The strength of Access and other relational databases is their ability to link data between tables.

You may find these links useful

http://www.datamodel.org/NormalizationRules.html

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[yhchen]

If you add a button on your subform and in it's click event your could add something like this:

Private Sub cmdtrans_Click()
    Me.Parent!student_Number = Me.Student_Number
    Me.Parent!Student_name = Me.Student_Name
End Sub

This will transfer the values from your unbound subform to the mainform, you can also add some If_Then_else statements before you transfer.

Hope this helps.

JR

Thanks JR...it looks like the code I need

unfortunately it doesn't work

is it possible to make the parent form specific? for example:

Private Sub cmdtrans_Click()
    Form![Log]student_Number = Me.Student_Number
    Form![Log]Student_name = Me.Student_Name
End Sub

only know very little about VBA code so if you could look at above and give me any advice will be very much appreciated...

 

[JANR]

Refrencing form/subforms can be a bit dicy sometimes here is a usefull webpage about the subject: http://www.mvps.org/access/forms/frm0031.htm

Print it out and tape it on your wall.

On the quick testbase I tested it it works, but without all the details it difficult to be spesific. I just pointed you in one direction, i'm sure you find something that works for your application. Best of luck

JR

 

[yhchen]

Refrencing form/subforms can be a bit dicy sometimes here is a usefull webpage about the subject: http://www.mvps.org/access/forms/frm0031.htm

Print it out and tape it on your wall.

On the quick testbase I tested it it works, but without all the details it difficult to be spesific. I just pointed you in one direction, i'm sure you find something that works for your application. Best of luck

JR

Dear Janar

with respect I am sure the code should work fine... the reason it didn't work in my form is .. (I can see more people shouting this is bad practice) the subform you see in the graphic actually contain another subform (so it's a sub-sub-form)

I had to do that so the txtsearch vba code could work (still strugle to have dlookup working...)

therefore when click on the icon, it will treat the serach field form as parent form instead of the log form you see in the picture.... (am I making sense?? if not, please let me know)

that's why I thought it'd help if it's possible to make the destination form specific instead of parent form.... if you know any other solution it'd be very much appreciated...

Many thanks in advance

 

[JANR]

Is it possible to post a samplebase with some data to test?

Also if you looked at the link I provided, you will find the syntax. If I would guess it would be:

Me.Parent.Parent!Student_Number = Me.Student_Number

Me is a shorthand version of Forms!Frmname!controlname

All refrence on forms/subforms depends on from where you are and execute from. sub->Main , Main->Sub->Sub, sub2->sub1 etc so what works one way don't work in reverse.

JR

 

[yhchen]

Is it possible to post a samplebase with some data to test?

Also if you looked at the link I provided, you will find the syntax. If I would guess it would be:

Me.Parent.Parent!Student_Number = Me.Student_Number

Me is a shorthand version of Forms!Frmname!controlname

All refrence on forms/subforms depends on from where you are and execute from. sub->Main , Main->Sub->Sub, sub2->sub1 etc so what works one way don't work in reverse.

JR

oh my god!! the parent.parent worked!!!

BUT!!!! Only student number was copied and pasted... I am not sure what happened... in your test database, were both fields copied and pasted??

(I will have a look at the link you enclosed...I am covering the front desk and working on database at the same time... I know, another bad practice)

Thank you very very much Janr, I really appreciate your help.

 

[JANR]

Glad it works

Yes in my test both moved, so provided you don't have naming issues it should work. What it does is to take the value of your sub-sub field and "copy" it to the parent form.

Check to see if one of the textfields in either form is called something else, and follow the syntax of your working event.

JR

 

[yhchen]

Glad it works

Yes in my test both moved, so provided you don't have naming issues it should work. What it does is to take the value of your sub-sub field and "copy" it to the parent form.

Check to see if one of the textfields in either form is called something else, and follow the syntax of your working event.

JR

Thank you JR. It wasn't a naming issue...

quite strangly, once I reverse the order: student_name first then student_number (only did this as to test if it only takes the first line syntax) then it just worked!! don't know why but it is indeed working!!

thank you so so so so so so so so much!!