Passing data from child form to the original form when both forms are open (1 Viewer)

[Mr_Si]

Hi All,

I'm not sure if this should go in here or in VBA/Modules, because it's to do with Forms, but it's VBA code.

I'm new here (first post - wooo!) and glad to have made the leap.
I think this may have been looked at before, but I'm not very good at using the search function. I've looked on google, but all I've seen doesn't seem to be for my use, so I came here as recommended by "RadioActiveFrog" who I believe is a member here and has had much good a thing to say.


Right, basically, I am writing an Access database for my company's ordering system.

I have a form called "Enquiry" which has many tabs, one of these is the "client" tab in in here are many controls (table fields):

Firstname
Surname
Company
Addressline1
Addressline2
Town
County
Postcode
Phones
Fax
AltMobile
Email
ContactType

Now, there is an option to enter a new client or search the database for an existing client and it is basically the "new client" which I'm having issue with at the moment.

Basically, clicking the button "New Client" opens another form called "Client". Here, the user enters the data into controls which are the same as above (but on this contact form).

When done, they click a button called "Save and Close" which then asks the user if they want to paste their entered data into the original main "Enquiry" form.

It's this last bit, the transferring of this data I can't get my head around and would be incredibly greatful for help with.

My Code is below:

[color:"blue"] 
Private Sub Save_Click()
On Error GoTo Err_Save_Click

    ' On clicking save, a dialogue box will open asking if you want to paste this data
    ' into the enquiry form. Clicking yes will do this. Clicking no will just close the box
    
    'declare intpress as an integer
    Dim SavePress As Integer
    
    'Save Command
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'when save and paste is clicked, ask if the information should be pasted into the form or not
    
    SavePress = MsgBox("Would you like to paste this Contact Information into the Enquiry Form?", vbQuestion + vbYesNo, "Paste details")
        If SavePress = 6 Then
        
        'If the enquiry form was the form which initiated the cotact form, then copy and paste
        'the informtion into the enquiry form and the close the form
        
            [color:"red"]Enquiry![/color]FIRSTNAME = Me.FIRSTNAME
            Enquiry!SURNAME = Me.SURNAME
            Enquiry!COMPANY = Me.COMPANY
            Enquiry!CATEGORY = Me.CATEGORY
            Enquiry!ADDRESSLINE1 = Me.ADDRESSLINE1
            Enquiry!ADDRESSLINE2 = Me.ADDRESSLINE2
            Enquiry!TOWN = Me.TOWN
            Enquiry!COUNTY = Me.COUNTY
            Enquiry!POSTCODE = Me.POSTCODE
            Enquiry!PHONES = Me.PHONES
            Enquiry!ALTMOBILE = Me.ALTMOBILE
            Enquiry!EMAIL = Me.EMAIL
                
            DoCmd.Close acForm, "Contact"
              
      Else
            
            DoCmd.Close acForm, "Contact"
                
      End If
    
    
Exit_Save_Click:
    Exit Sub
[/color]

But It's not working. It's giving an error saying "Compile Error: Variable not defined" and it highlights the word which I've mad RED in the code above. Now, I tried changing the exclamation for a fullstop, and I also tried writing "Form.Enquiry.FIRSTNAME" (and also with exclamation marks). None have given a working result.

I'll try and get some print screens too for more visibleness (new made-up word there) so you can see what I mean.


Blessings,
Si


Edit: here are a couple of print screens:

1. The Client tab/page of the main enquiry form:

[image]http://i6.photobucket.com/albums/y218/Mr_Si/Enquiry.jpg[/image]


2. The Client Details form, which opens as a result of pressing the "New Client" button in the main enquiry form (shown in the background):

[image]http://i6.photobucket.com/albums/y218/Mr_Si/Client.jpg[/image]

 

[RuralGuy]

Are you aware that forms do not contain data, tables do? Forms are just a window into the table. I would think that your second form was entering data into a table that is already referenced by the first form. Therefore all you need do is refresh the first form and Taa Daa, there's all the data you just entered in the second form. Having said that, you can reference controls on another open form by going through the Forms collection.
Forms!Enquiry!FIRSTNAME = Me.FIRSTNAME

 

[Mr_Si]

Many thanks RuralGuy,

Yes, I was discussing it with a friend who's done a few, (he came round to drop something off and I showed him what I was wanting to do).

His comments were similar to yours in that both tables are referencing the same table and so it is actually an in-efficient way to add information and why didn't I just add it in the "Enquiry" form. So that's what I'm going to do

Thanks for that reference though. What you said with regard to Forms!Enquiry!FIRSTNAME = Me.FIRSTNAME confirms what was said over in the UtterAccess forums.

Thanks again!

Si

 

[RuralGuy]

There is absolutely nothing wrong with two open forms referencing the same table. Often it is clearer to the user what is required if you open a separate form for the function requested. The point is that when you close the second form, the data just entered is already in the RecordSource of the first form and a simple Requery or Refresh will make it visible in the RecordSet.