Using a data entry form to update a subform

[Guest]

Hi there - I'm pulling my hair out here!

I have a main form with audit details, a subform that lists the
non-conformances relating to that particular audit and would like to use a
data entry form to enter new non-conformances into the subform.

I currently have the subform as not enabled (it acts as a list) which only
has a few of the fields shown - the complete data for the non-conformance is
entered onto a separate form.

The main form and subform are connected by the AuditID field.

I can't get my data entry form to appear in data add mode and automatically
populate the audit ID based on the main form data. Any ideas?

Thanks

 

[Damon Heron]

Call your data entry form with a command button on your main form, using
openargs:

Click event would be
dim stDocName as string
stDocName= Your data entry form name

DoCmd.openform stDocName, acNormal, , , acFormAdd, acDialog, AuditID.Value
'when your data entry form is closed, then the main form's subform is
requeried...

Forms!YourMainform.yoursubform.Requery
End Sub

In the data entry form's Open event, put
if not isnull(me.openargs) then
Your AuditID. rowsource= me.openargs
else
'......
end if

Damon

 

[Guest]

Hi Damon,

Thanks for the advice - however I get a runtime error - "Object Required" -
when I click the button.

The code for the button is:

Private Sub cmdAddNewObs_Click()
Dim stDocName As String
stDocName = frmObsDetail 'my data entry form

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog, AuditID.Value
Forms!frmAuditDetails.subfrmObs.Requery 'my subform is requeried

End Sub

And the open event code on the data entry form is:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Forms!frmAuditDetails.AuditID.RowSource = Me.OpenArgs
Else
DoCmd.OpenForm FormName:="frmObsDetail", DataMode:=acFormEdit
Exit Sub
End If

End Sub

The Debug highlights:
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog, AuditID.Value

I've obviously missed something but I can't work out what!

Hope you can help,
Thanks

 

[Damon Heron]

It is the AuditID -- should correspond to the same ID on both forms. Have
you tried just "AuditID" and dropping the value word?

Damon

 

[Guest]

Hi Damon,

Yep, I get another error on the following line - "The action or method
requires a Form Name argument"

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog, AuditID

???

 

[Damon Heron]

try typing in the actual form name, instead of stDocName - in quotes, like
"frmObsDetail", although I don't know why
it fails on the name. It works fine on my db.
Damon

 

[Damon Heron]

Hi Damon,

Yep, I get another error on the following line - "The action or method
requires a Form Name argument"

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog, AuditID

???

 

[Damon Heron]

Also try eliminating the openargs value--
DoCmd.openform "frmObsDetail", acNormal, , , acFormAdd, acDialog
If that doesn't open the form, then there is something wrong with your form
name or the properties of the form.

Damon

 

[Guest]

Hi Damon,

I now get the error "Method or Data member not found" on the following line
on the data entry form's Open event:

Me.txtAuditID.RowSource = Me.OpenArgs

I don't have any hair left ...

 

[Guest]

Hi Damon,

I just can't get this to work - this gives me an error message "Method or
data member not found" on the data entry form's Open event:

Me.txtAuditID.RowSource = Me.OpenArgs

Is there another way I can store the AuditID and then when I open the data
entry form, call the stored ID and place it in the AuditID on the data entry
form?

Would the structure of my database have anything to do with it?
frmAuditDetails (main form), subfrmObs (subform) and frmObsDetail (data entry
form) all reference a table not a query and the AuditID from the main table
is the foreign key in the other tables.

Would queries help?

Thanks for your patience!

 

[Damon Heron]

I am sorry I led you astray with a couple of mistakes. This is what I
understand now. You have a main form bound to a table with AuditID as a
primary key field in the table. You want to open a form for data entry only
that has a foreign key of AuditID related to the first table.
In the command button's event on the first main form, enter
Dim stDocName As String
stDocName = "your form name" 'in quotes
DoCmd.openform stDocName, acNormal, , , acFormAdd, acDialog, AuditID

in the form you are using for data entry (properties on form dataentry=yes),
in the form's LOAD event (I said open event before, sorry), put:

Dim myID As Integer
myID = Nz(Me.OpenArgs, 0)
If myID > 0 Then
Me.AuditID = Me.OpenArgs
End If

This must work!
Now to have the info show up on your main form, after the DoCmd.Openform
line, put a requery of your subform.

Damon

 

[Guest]

Hi Damon,

It was the quotes!

It works beautifully - thanks for your help!