Child Form - Cannot Create Record

[CementCarver]

I have a parent table called BUSINESS, which has a autonumbered primary key called MEMBER_ID. The child table, called GROWER, is linked to the parent via the BUSINESS.MEMBER_ID = GROWER.MEMBER_ID. The MEMBER_ID field is a number, but after setting the referential integrity in the relationship diagram, the GROWER record needs to be created first. As well, the GROWER table's primary key is called FARM_NUM, which is a number which needs to be incremented everytime a new record is created.

So, the code I've attempted, first determines the next BUSINESS.MEMBER_ID to be assigned, then determines the next highest FARM_NUM to be assigned.

After my client determines that a new GROWER record needs to be created, the "Add New Grower Record" command box is selected, with a blank frm_CREATE_FULLGROWER form displayed. I was hoping to populate the FARM_NUM automatically, after the client inputs a new BUSINESS NAME.

The code currently creates the record in the BUSINESS table, but does not create the GROWER record, nor does it populate the FARM_Num.

Can anyone see what I'm doing wrong with the code below? Thanks in advance. CementCarver


Private Sub Create_Grower_Record_Click()

Dim rstGrower As Recordset
Dim strBUSINESS_NAME As String
Dim IntFarm_num As Integer


Dim IntMax_Mem As Integer
Dim IntBusMax As Integer

Dim rst1 As DAO.Recordset, strMax_Mem As Integer

IntBusMax = DMax("MEMBER_ID", "BUSINESS")
IntBusMax = IntBusMax + 1

IntFarm_num = DMax("FARM_NO", "FULLGROWER")
IntFarm_num = IntFarm_num + 1

Debug.Print IntFarm_num

'DoCmd.OpenForm "frm_CREATE_FULLGROWER", , , , acFormAdd

'DoCmd.OpenForm "frm_CREATE_FULLGROWER", acNormal, , , acFormAdd, , "IntFarm_num"

'DoCmd.OpenForm "frm_CREATE_FULLGROWER", , , , acFormAdd, , IntFarm_num
', "Add"

DoCmd.OpenForm "frm_CREATE_FULLGROWER", acNormal, , "[Farm_num] = " & "IntFarm_num"


Set db = CurrentDb
Set rstGrower = db.OpenRecordset("FULLGROWER")

' Get new data for record.
strBUSINESS_NAME = Trim(InputBox(BUSINESS_NAME))

'Forms![yourformname].Requery
' Get new data for record.
strBUSINESS_NAME = [Forms]![frm_CREATE_FULLGROWER]![BUSINESS NAME]

IntFarm_num = Forms![frm_CREATE_FULLGROWER]![Farm_No]

IntMax_Mem = IntBusMax

' Proceed if the user entered something for both fields.
If strBUSINESS_NAME <> "" Then
' Update record with new data.
EditName rstGrower, strBUSINESS_NAME, IntFarm_num, IntMax_Mem

Else
Debug.Print _
"You must input a business name!"
End If

rstGrower.Close
dbsNorthwind.Close

End Sub

Sub EditName(rstGrower As Recordset, _
strBUSINESS_NAME As String, IntFarm_num As Integer, IntMax_Mem As Integer)

' Make changes to record and set the bookmark to keep
' the same record current.
With rstGrower
.AddNew
![BUSINESS NAME] = strBUSINESS_NAME
![Farm No] = IntFarm_num
![MEMBER_ID] = strMax_Mem
.Update
.Bookmark = .LastModified
End With

End Sub

[orange]

I don't know for sure that I'm following your description, but here's the general concept about parent/child relationships.

The best analogy is to consider a Parent and a Child.

You can't have a child until you have identified the Parent. This is basic referential integrity.

A Parent can have 1 or more Children.

But you can't just have a Children (child). Access and other database systems prevent this Orphan.

Access Form and subform concept is used most often with tables that have a 1 to Many relationship.

You identify the Parent in the main form, and in the subform you can add 1 or more children for this Parent. But as soon as you try to add a Child before you have identified the Parent--->ERRoRR!!!! No can do.

For anyone reading this post:

Referential integrity is a system of rules that ensure relationships between rows in related tables are valid and that you do not accidentally delete or change related data.

see http://msdn.microsoft.com/en-us/library/kcyehfs6.aspx

[CementCarver]

Thx Orange,

It took me most of the day, but after having a similar concept, like the one you described, I was able to successful solve my own problem. Thanks for your return comments/description.

CementCarver