acFormAdd works - new record doesn't

[Angi]

I hope I explained that right. This is just too weird for me. My
company entry form has an Add cmd btn on it. When clicked, it's
supposed to open the CompanyMain form with the new record that was just
entered. When I use the Add New Company button on my CompanyMain form,
it opens the entry form in Add mode and closes the CompanyMain form.
Then I click the Add btn on the entry form and it saves the curr
record, re-opens the CompanyMain form with the new record and closes
itself.

If I just open the company entry form so that I can view all the
companies and then try to add a new record, it opens the CompanyMain
form but uses the first company record rather than the new one.
Furthermore, I should be able to click the add btn to fire the code and
open with the current record (new or not). That's not working either.
I can't figure out why this is happening. Sorry this sounds so
confusing.

Here's the code for the company entry form:
Private Sub cmdAdd_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CompanyMain"

stLinkCriteria = "[CoID]=" & Me![CoID]
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , , , , Me.CoID
DoCmd.Close acForm, "company", acSavePrompt

End Sub


Here's the OnOpen event for CompanyMain:
Private Sub Form_Open(Cancel As Integer)
If (Not IsNull(Me.OpenArgs)) Then
Me!CoID.DefaultValue = Me.OpenArgs
End If
End Sub

 

[Penguin]

You are using stLinkCriteria but where are you putting this? You are
not opening a form to this record?

Private Sub cmdAdd_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CompanyMain"

stLinkCriteria = "[CoID]=" & Me![CoID]
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , , , , Me.CoID
DoCmd.Close acForm, "company", acSavePrompt

End Sub

 

[Angi]

sorry about that. Old code I never took out. I was told to use the
openargs instead to set the other form's CoID.

 

[Angi]

I took out the stLinkCriteria line. Obviously it didn't make a
difference. I'm still stumped. I've been working on it all night. I
thought maybe the SaveRecord was the problem, but it saves it either
way, so that's not it. I'm drawing a blank! Help! please!!!! <g>

 

[Penguin]

OK, you have two forms one to view data and one to add data. Why not
use one form for both? And if you need both forms, once you have the
record ID then use the open form method to view your current record.

You are using stLinkCriteria but where are you putting this? You are
not opening a form to this record?

Private Sub cmdAdd_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CompanyMain"

stLinkCriteria = "[CoID]=" & Me![CoID]
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , , , , Me.CoID
DoCmd.Close acForm, "company", acSavePrompt

End Sub

I hope I explained that right. This is just too weird for me. My
company entry form has an Add cmd btn on it. When clicked, it's
supposed to open the CompanyMain form with the new record that was just
entered. When I use the Add New Company button on my CompanyMain form,
it opens the entry form in Add mode and closes the CompanyMain form.
Then I click the Add btn on the entry form and it saves the curr
record, re-opens the CompanyMain form with the new record and closes
itself.

If I just open the company entry form so that I can view all the
companies and then try to add a new record, it opens the CompanyMain
form but uses the first company record rather than the new one.
Furthermore, I should be able to click the add btn to fire the code and
open with the current record (new or not). That's not working either.
I can't figure out why this is happening. Sorry this sounds so
confusing.

Here's the code for the company entry form:
Private Sub cmdAdd_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CompanyMain"

stLinkCriteria = "[CoID]=" & Me![CoID]
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , , , , Me.CoID
DoCmd.Close acForm, "company", acSavePrompt

End Sub


Here's the OnOpen event for CompanyMain:
Private Sub Form_Open(Cancel As Integer)
If (Not IsNull(Me.OpenArgs)) Then
Me!CoID.DefaultValue = Me.OpenArgs
End If
End Sub

 

[Angi]

Thanks for the reply! I was beginning to think I was blacklisted! <g>


The main frm is a read-only with 3 other subforms...that's why there's
2 forms. As far as using the record ID and open form method, isn't
that what I'm doing?? I'm saying to open the main form with the value
of me.coid. It works, but only if I'm in data-entry mode, not adding a
new record with the navigation buttons. That's why I'm so stumped! If
something is wrong with my syntax, please let me know.

Just as an afterthought, it seems like if there's only one record to
choose from, it's ok. If there's more than one, it takes the first.
I've tried changing the openargs to forms!company.coid...but that
didn't work either. I guess I'm confused as to the difference between
me.coid and "currentrecord".coid. Thanks for your help!

 

[Angi]

Just an update:

I didn't realize this code was returning a blank form even in
data-entry mode.
So...I tested this form with two codes. One for the cmdAdd Onclick
event and the Form_Close event. The only change I made was using the
stLinkCriteria in the Where for openform instead of openargs for the
Form_Close event. It worked. But again...only in data entry mode. If
I just open the form and scroll through the records, I still can't
bring up the current record. Which brings me back to my previous post,

I guess I'm confused as to the difference between me.coid and

"currentrecord".coid.

And now it raises a new question on the same topic...why did the Where
statement work and not the openargs?? What's the difference and is one
better to use instead of the other?

 

[Penguin]

Angi, I only use the Where statement to link back or open a form to a
certain record. As far as the current record you may need to save the
record first and then reopen the form.

 

[Angi]

I don't mean to keep beating a dead horse, but I am saving the record
first.

DoCmd.RunCommand acCmdSaveRecord THEN
docmd.openform...blah blah blah

And I am now using the Where instead of the openargs, but it still
won't open to a specific record unless it's a new record in data-entry
mode only. What am I missing????

Thanks for all your help...I really do appreciate it. This is driving
me crazy!

 

[Penguin]

Direct from help file for Access.

You can use the DataEntry property to specify whether a bound form
opens to allow data entry only. The Data Entry property doesn't
determine whether records can be added; it only determines whether
existing records are displayed.

The DataEntry property uses the following settings.

Setting Description Visual Basic
Yes The form opens showing only a blank record. True (-1)
No (Default) The form opens showing existing records. False (0)

This means you can not view existings records while in data entry
mode. Try using this code: Me.DataEntry = False

 

[Angi]

Penguin,
My DataEntry property is set to no. I was using that to explain the
difference of the views. The cmdAdd button opens the form in acFormAdd
mode which only allows new records so I was using the term data entry
mode because it's the same thing...at that time. It's when I view all
records that it doesn't work. It won't pull up the current record that
I am. That's what I'm trying to figure out.

 

[Penguin]

I am a little confused. Do you need to view a certain record or add a
new record?

 

[Angi]

Penguin,
I'm using the same form for both. You can add a new record from
another form, but if I open the form and scroll through the records, it
should open the mainform when I close the form. (The OnClose event and
cmdAddbtn have the same code.) The add btn is only visible if opened
from the other forms in add mode. That's why I was wondering if there
was a difference between me.coid and "currentrecord".coid. Sorry I
confused you. I can see it and I'm trying to give you a visualization,
but I'm frustrated with this and tend to skip stuff or ramble. I hope
this helps. Thanks so much!

Ang

 

[Penguin]

OK if I understand this correctly. You are using two seperate forms
with the same layout. One for viewing data and one for entering new
data. Why not use one form for both? This would be less complicated
and less coding is needed.

 

[Angi]

Casey (is that right?),
You actually asked me that question earlier in the post (been so long!
<g>) and my answer was:

The main frm is a read-only with 3 other subforms...that's why there's
2 forms.

The main reason behind that is the fields on the main form are
concatenated (City, State, Zip) all on one line. Plus I needed to code
the email fields, etc.

The main frm is supposed to be a filtered form from the Co entry form.
It's filtering, but always to the first record, not the the current
record (unless there's only one record). Which is why it works with
cmdAdd btn ("data-entry mode"). I have it setup so that you can only
add one company at a time so that's why there's only ever one record
when done this way. Which.....brings me back to the big question...how
do I say "currentrecord".coid instead of me.coid? Confused yet???

Thanks for all your time! I really, really do greatly appreciate
it!!!! (I don't mean to be difficult, honestly)

 

[rkc]

The main frm is supposed to be a filtered form from the Co entry form.
It's filtering, but always to the first record, not the the current
record (unless there's only one record). Which is why it works with
cmdAdd btn ("data-entry mode"). I have it setup so that you can only
add one company at a time so that's why there's only ever one record
when done this way. Which.....brings me back to the big question...how
do I say "currentrecord".coid instead of me.coid? Confused yet???

Thanks for all your time! I really, really do greatly appreciate
it!!!! (I don't mean to be difficult, honestly)

Isn't there a command button wizard that walks you through opening
a form and going to a specific record based on matching fields in
the record source of both form's? Seems to me that's all you are
trying to do.

 

[Penguin]

Angi, I think your wording is confusing me. First when you say current
record I think you mean the last entered record right? The current
record true meaning is the record being edited on the screen. Your
form filter is probably the problem. Do you want to sort the records
in ascending or decending order?

Also when you mention "currentrecord".coid the " ." (dot) refers to a
property. I don't think there is a property named .coid
Are you looking for a referrence to the CurrentRecord Property?

 

[Angi]

AccVer 2002 (2000 file format).

Sorry about my wording. I'm messing you up with the current record
reference. What I mean is the record I'm currently looking at, not
necessarily editing.

ie: 20 records, I'm on 7. I want it to pull up record 7 when I close
the form. It always pulls up record 1. The CoID is unique so I can
use that as a reference, or at least should be able to. Which is why I
don't understand why it works when adding, but not viewing.

Also when you mention "currentrecord".coid the " ." (dot) refers to a
property. I don't think there is a property named .coid
Are you looking for a referrence to the CurrentRecord Property?

Yes, I am. That's why I put it in quotes. I didn't know of another
way to say This Record's CoID. I realize I'm having a problem with
this, but I do know that there isn't a property CoID. ;-) Even if I
click on a field to give that record focus, it still doesn't work. I
hope I'm explaining this better. Thanks for all your time.

Angi

 

[Penguin]

OK, I think were going to get this right tonight. Try this code in the
form you are closing or searching. Put this in the button you use to
close the form.

DoCmd.OpenForm "MyForm"
Forms!MyForm.RecordsetClone.FindFirst "[RecordID] = '" & Me![RecordID]
& "'"
Forms!MyForm.Bookmark = Forms!MyForm.RecordsetClone.Bookmark
DoCmd.Close acForm, "MyOtherForm"

Replace MyForm & MyOtherForm with your form names and RecordID with
your record id or primary key.

Lets hope this works for you.

 

[Angi]

OK....good news and bad news.

Good It works.

Bad I get 2 errors.

first one was data type mismatch on line
Forms!companymain.RecordsetClone.FindFirst "[CoID] = '" & Me![CoID] & "
'"

Once I made it:
Forms!companymain.RecordsetClone.FindFirst [CoID] = " & Me![CoID] & "

it worked. Don't know why, but I'm not complaining.

Second error is The Close action is canceled. #2501
DoCmd.Close acForm, "company"