Referring to controls on the Main Form and another Subform

P

Peter Stone

Novice

I had the following code on a tab page.

Private Sub lblSelectNewRecord_DblClick(Cancel As Integer)
'select a new record by double clicking on the label
DoCmd.GoToRecord , , acNewRec
Me!LocalDestinationID = Me.cboDestination
Me!fsubHousekeeping!cmdPublishUpdate.Caption = "Publish"
Me.Refresh
End Sub

When I changed to the page to a subform I changed the code to the following,
but it doesn't work.

Now I get:
Run-time error '3164':
Field cannot be updated

Run-time error '2465':
Can't find the field "fsubHousekeeping' referred to in your expression.

Private Sub lblSelectNewRecord_DblClick(Cancel As Integer)
'select a new record by double clicking on the label
DoCmd.GoToRecord , , acNewRec
Me!LocalDestinationID = Me.Parent!cboDestination (error 3164)
Me!fsubHousekeeping.Form!cmdPublishUpdate.Caption = "Publish" (error
2465)
Me.Refresh
End Sub

Thank you

Peter
 
S

strive4peace

Hi Peter,

"I had the following code on a tab page..."

Tab pages do not have a source object, they are only used to organize
things, so it is not clear where you actually had the code ... I am
guessing that is is behind your mainform.

Now, I am guessing that you made your mainform a subform... so you need
to now move the code to be behind the subform object for it to work the
same way.

If you are filling out a new record and have not yet saved it, you need
to do this before Access will create another new record:

if me.dirty then me.dirty = false

is the method I like best for testing to see if something needs to be
saved and then saving it if changes were made.

then, if you want to make a new record

if not me.newrecord then DoCmd.RunCommand acCmdRecordsGoToNew

(there is always more than one way to do things, your way works too)

"When I changed to the page to a subform I changed the code to the
following, but it doesn't work."

your username is "novice", so I imagine you don't understand code too
well... if I am wrong, please forgive me.

where is the label? on the mainform or the subform?

can you provide more details please?



Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*
 
P

Peter Stone

Sorry I should have made it a little clearer. Originally, the code was on a
tab page of the main form. I converted the tab page into a subform based on a
query.

cboDestination is on the header of the main form
cmdPublishUpdate is on another subform (fsubHousekeeping)

You are correct, I am a novice at coding VBA, but thanks to help on this
forum, I've constructed some user-friendly forms that accomplish what I want.

Thanks for the tip about how to check if changes need to be saved.
 
S

strive4peace

Hi Peter,
cboDestination is on the header of the main form
cmdPublishUpdate is on another subform (fsubHousekeeping)

since using the DoCmd method to add a record does not let you specify
WHERE you want the record added,,, there is yet another way...

recordset.addnew

if you are in:

1. code behind form

me.recordset.addnew

2. code behind mainform and act on subform

me.subform_controlname.form.recordset.addnew

3. code behind subform1 and act on subform2

me.parent.subform2_controlname.form.recordset.addnew

'~~~~~~~~~~

"Me!LocalDestinationID = Me.Parent!cboDestination (error 3164)"

do you have a control with the NAME property --->
Me!LocalDestinationID
on the form you are behind? Name and ControlSource are not the same...I
like to make the name the same though, for bound controls

Is the bound column of cboDestination = to the ID field?

If so, you are not testing to make sure it is filled out before using it
to make an assignment

If Not IsNull(Me.Parent!cboDestination) then
Me.LocalDestinationID_controlname = Me.Parent.cboDestination
End If

"Me!fsubHousekeeping.Form!cmdPublishUpdate.Caption = "Publish" (error
2465)"

"cmdPublishUpdate is on another subform (fsubHousekeeping)"


Because you have not specified WHERE you are launching the code, I do
not know how to correct your reference problem...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*
 
P

Peter Stone

Crystal
Sorry to be so slow replying. I've sought help from several areas on this
question and no result.

I've rebuilt my form and am rechecking my design. I will repost the question
with a much more detailed explanation when I've finished.

Thanks for taking the time.

Regards

Peter
 
S

strive4peace

Hi Peter,

ok ;)

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
P

Peter Stone

Thank you for your patience Crystal.

You solved my problem. Most appreciated.

recordset.addnew was the answer. In case another novice looks at this I was
referring to
1. The record set on the main form from subform1.
2. The button on another subform from subform1.
Private Sub lblSelectNewRecord_DblClick(Cancel As Integer)
'select a new record by double clicking on the label
Me.Parent.Recordset.AddNew
Me.Parent!LocalDestinationID = Me.Parent!cboDestination
'set the caption on the Publish/Update button to PUBLISH
Forms!frmText!fsubHousekeeping.Form!cmdPublishUpdate.Caption = "Publish"
Me.Parent.Refresh
Me.lstSelectRecord.Requery
End Sub
 
S

strive4peace

you're welcome, Peter ;) happy to help


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top