code isn't working..don't know why

[Angi]

Last question...for now!

I've created an Add Contact button on my company form. All my tables
are linked by CoID. When I click on the button, it's supposed to open
the ContactMain form in Add mode and populate the CoID with the current
CoID. It opens the form, but it doesn't carry over the CoID. This
code works other places in my db. What am I doing wrong???

Private Sub cmdAddContact_Click()
DoCmd.OpenForm "ContactMain", , , "[CoID]=" & Me![CompMain.CoID],
acFormAdd

End Sub

TIA

 

[Rick Brandt]

Last question...for now!

I've created an Add Contact button on my company form. All my tables
are linked by CoID. When I click on the button, it's supposed to open
the ContactMain form in Add mode and populate the CoID with the
current CoID. It opens the form, but it doesn't carry over the CoID.
This code works other places in my db. What am I doing wrong???

Private Sub cmdAddContact_Click()
DoCmd.OpenForm "ContactMain", , , "[CoID]=" & Me![CompMain.CoID],
acFormAdd

End Sub

TIA

That code says to open the new form filtered to show *existing* records with a
certain [CoID] value. It does nothing to determine or set the [CoID] value
should you decide to create a new record on the second form.

 

[Guest]

Angi,

What you are doing wrong is that you are creating a command that opens the
form in AddMode which tells the form to open at the end of the recordset. In
the same instance you are trying to apply a filter to the form to display
records with the "CoID".

So when you get the form open at a new record you need to determine the
method that works best for you to get the new record populated with the
desired data. There are many ways of doing this including sending the data
as an openarg then populating a new record with the arg in the OnOpen event.

Best of luck!

 

[Angi]

Rick,
Thanks for the quick reply. Ok, so opening the form in Add mode
doesn't tell it to carry the CoID over. Here comes the stupid
question...what will?? Do I have to put code in the OnLoad event of
the new form to make it look for the CoID on the main form?

Thanks!
Angi

 

[Rick Brandt]

Rick,
Thanks for the quick reply. Ok, so opening the form in Add mode
doesn't tell it to carry the CoID over. Here comes the stupid
question...what will?? Do I have to put code in the OnLoad event of
the new form to make it look for the CoID on the main form?

Thanks!
Angi

The only built in way to automatically fill in a linked field is with the use of
an embedded subform which will pick up the linked values of the parent form.

You could do the following...

Include the CoID value as an OpenArgs value.

DoCmd.OpenForm "ContactMain",,,,,,Me.CoID

....then in the Open event of the second form...

Me.CoID.DefaultValue = Me.OpenArgs

 

[Angi]

OK...this is my new code:

On ContactMain's OnOpen event:
Private Sub Form_Open(Cancel As Integer)
Me![CoID] = Forms!CompanyMain![CompMain.CoID]
End Sub

Now I'm getting the following error:
Run-time error '2448':
You can't assign a value to this object.


I've clicked on the help, but nothing comes up. I changed the OnClick
for the button to just open the form in Add mode with no criteria.
I've changed CompanyMain to [CompanyMain], but nothing changed. NOW
what am I doing wrong!

Thanks to you both for all your help!

 

[Rick Brandt]

OK...this is my new code:

On ContactMain's OnOpen event:
Private Sub Form_Open(Cancel As Integer)
Me![CoID] = Forms!CompanyMain![CompMain.CoID]
End Sub

Now I'm getting the following error:
Run-time error '2448':
You can't assign a value to this object.


I've clicked on the help, but nothing comes up. I changed the OnClick
for the button to just open the form in Add mode with no criteria.
I've changed CompanyMain to [CompanyMain], but nothing changed. NOW
what am I doing wrong!

Thanks to you both for all your help!

Try Load event instead of Open.

 

[Guest]

Angi,

Rick's answer is a bit too simple and could get you into trouble. If you
choose to use the OpenArgs to pass you data and then populate the new record
you might want to do some checks first...

because you may use this form for more than dataentry or you may wish to add
more records while the form is open you may want to do something like this...


if(not isnull(me.openargs)) then
if(me.recordsetclone.eof) then
me.ControlName = me.OpenArg
end if
end if

or

if(not isnull(me.openargs)) then
With me.recordsetclone
.AddNew
.FieldName = me.openargs
.Update
End With
End If

 

[Guest]

Angi,

See my not attached just after the previous from Rick....

 

[Angi]

Thank you thank you thank you!!!! That worked! I don't know why, but
it worked! I don't understand the difference between the two you gave
me. Is there an advantage of using one over the other?

Furthermore, the openargs will never be empty and the form will always
be opened in add mode, so is all that code really necessary?

Sorry for all the questions, but feel good about yourselves that you're
teaching me!

 

[Angi]

OK...one more related thing. I will need to requery the main form when
the contact entry is done. I've tried docmd.requery
Forms!CompanyMain...but that didn't work. Technically it's the subform
that needs to be requeried, but i don't care how i do it as long as the
person just added shows up.

 

[Guest]

Angi,

First a comment about your previous entry.... The openargs will only have
data if send data via the OpenForm command. But no you don't necessarilly
need that check. But if you don't check it Null values can cause you
trouble.

Second... answering your question about advantages of one method over the
other. The first method does not update the record, instead it only
populates the field that you want to add data to. It is then your
responsiblity to save the record. This is advantages if you have more data
to add to the record.

In the second method the record is created and updated/saved. The form at
this point has not been updated to reflect the changes. So if this is the
method that works for you and you want to see the record then you would do
this...

first requery the form
me.requery

Then goto the new record...
With me.recordsetclone
.FindFirst "[FieldName]=" & value
(Note: if the CoId is a string then enclose the value variable with quotes
like so e.g. Chr(34) & Value & Chr(34)

if(not .NoMatch) then
me.BookMark = .BookMark
end if
End With

 

[Angi]

Devlin,
Thank you for the explanations. I chose to use the second one. I used
the code for the requery, but something's not right. The form for the
Add is not a subform on my main form. I've been reading the help but
it doesn't explain how to requery the form that isn't active, just that
I need to use the Requery method instead of docmd.requery. It suggests
a macro but I'd prefer VBA instead. I'm pretty sure it has to go in
the On Close event on the Add form, but I'm still trying to figure out
the syntax.

 

[Guest]

Angi,

I am aware that you are working with data in one form (i.e. no subform). I
will write the code here for you and this will work.

First off I recommend using the first method, but if you prefer the second
then here we go.

Private Sub Form_Open(Cancel As Integer)
If (Not IsNull(Me.OpenArgs)) Then
With Me.RecordsetClone
.AddNew
.Fields!FieldName = Me.OpenArgs
.Update
Me.Refresh
Me.Bookmark = .LastModified
End With
End If
End Sub

 

[Guest]

Angi,

Here is another thing to consider. If when you are calling the form (i.e.
opening the form.) If the caller is generating some unique ID, you might
consider generating the ID directly in the form being calling, rather than
passing one along. Since I don't know what you are doing specifically I wish
you the best with you work.

Good Luck!

 

[Angi]

I promise I'll leave you alone once i get this going! Please don't be
mad! I copy, pasted and updated the code above. It's still not
refreshing the main form when I close the Add form. There doesn't need
to be anything in the On Close event of the add form? Again, sorry for
all the questions. I see you're pretty busy on here right now.

Thanks again..and again!
Ang

 

[John Vinson]

I promise I'll leave you alone once i get this going! Please don't be
mad! I copy, pasted and updated the code above. It's still not
refreshing the main form when I close the Add form. There doesn't need
to be anything in the On Close event of the add form?

I'll jump in for Devlin here:

Forms!mainformname.Requery


John W. Vinson[MVP]

 

[Angi]

thank you, john! all done now!