How to create macros that will help in doing functions in Forms.

[Judianne]

My database holds records related to Kids. It contains key metrics on the Kids. This data is colleced in the KidDataForm and stored in teh Kid table.

The datbase has a table to collect data on Mothers, called Mother table. Mothers can have multiple Kids. If Mother data is required, it is collected in the MotherDataForm.

The KidDataForm has a link to the MotherDataForm. That link is a control called "Mother Name". Mother Name is the primary key for the Mother table.

From time to time, a new record in the Mother table has to be added. At present, the process is cumbersome:
1) Click Button to open MotherDataForm (opens to new record, since there is currently no link).
2) Input name of Mother in MotherDataForm
3) Save the records.
4) Refresh Database
5) Return to KidDataForm
6) Select the name of the Mother, which should now appear in the "Mother" control drop-down box.
7) Go back to the MotherDataForm and update the record on the Mother.

What I want to know is HOW I can create a macro (which I would attach to a new button), that would allow me have Access automatically populate and link the tables. This is what I want to do:
1) Type name of Mother in the Mother control drop-down box. (I can do this now.)
2) Have access automatically copy that name into the Mother table as a new record and then open the MOtherDataForm on that record.

I cannot find any code (that I can understand) to do this. I would love it if someone could show me some example code and what each line is doing.

[zmbd]

Which version of Access are you using?

As for Macros:
You'll not find much help here for "Macros" as most of us use the VBA language to handle things like this due to the robustness of the programing environment.

[Judianne]

Is there VBA language that would accomplish this? I sometimes (think?) I have used Visual Basic for Excel.

[Judianne]

I found some code on a similar help website, which I think accomplishes similar to what I want to do, but I am having a very hard time making heads or tails of it. I put ' marks in front of how I am interpreting this. But - I still find it totally confusing and am not sure how I should adjust it for my database.

Private Sub cmdSend_Click()
On Error GoTo ProcError 
 
Dim db As DAO.Database
'What is this doing with "DAO.Database"
Dim rst As DAO.Recordset
'What is this doing with "DAO.Recordset"
Dim strTextData As String 
strTextData = txtBox 
'I think this is making my variable StrTextData equal to what is in the text Box.  But, how does the macro know to which text box on the form I am referring?
Set db = CurrentDb 
'db is the database I am talking about.
Set rst = db.OpenRecordset("tblTest",dbOpenDynaset) 
'rst is the Table, which I have named tblTest in my database?
rst.AddNew 
'Is this a known command to add a new record?
rst!fldTest = varTextData 
'Should this actually be strTextdata?
'Is the fldTest mean that the field I want in the table is called "test"
rst.Update Me!txtBox = "" 
'I don't understand this at all.
 
ExitProc: 'Cleanup 
If Not rst Is Nothing Then rst.Close: 
Set rst = Nothing 
End If
'Confused.
 
Set db = Nothing
Exit Sub
'confused
 
ProcError: MsgBox "Error " & Err.Number & ": " & Err.Description, _ vbCritical, "Error in procedure cmdSend_Click..." 
Resume ExitProc
 
End Sub

[Judianne]

ALSO: I am using Access 2010

[zmbd]

V2010 - yea.

Now, I'm not a big fan of this; however, let's try the following for your form as I get the impression that VBA isn't something you are very comfortable.

As you have a form that you are already using for data entry for the mother's information " MotherDataForm" we're going to stick with that.

Open the child's information form in design mode.
Right click the control for the mother's name and select the properties option in the pop-up.
Select the data tab
- part way down set the limit to list to yes
- just below that is "list items edit form" click on this and select the form MotherDataForm

Save, close the forms, re-open the child's information form...
If you click the drop-arrow in the combobox for the mother's name, you'll see a faint "pencil-dialog/form" shadow, you can click that and it will take you to the motherdataform or enter a new mothers's name that is not in the list and you will be asked about editing the list information.
Either way, the motherdataform will be opened. You can add all of the mother's information; however, you will have to close it to go any further.
If you don't have a close button on the motherdataform you can right click and close.
This will bring you back to the child's information form and the new item will be there in the list.

This method has some roughness to it; so, if it isn't just exactly what you want we can work on a full blown VBA approach.

As for the code you posted... it's for a Command Button and it could be made to work; however, it isn't quite the method I would have used to get things to happen; however, it does do the job. - However, I'm going to ignore it for the moment.

[Judianne]

Zmbd - I am so thankful for your help. Unfortunately, I had a few other "fire drills" today to handle. I am excited to test this out and see how it works. I will report back. Thank you again!!

[zmbd]

I can really understand that - I've been having those pop-up in the lab for the past month!

I'm on a retreat tomorrow so if you post and don't hear from me please don't panic - take out your towel and the Peril Sensitive Sunglasses and one us will be along shortly. ;-)