Subform creates two records

[MsfStl]

Hi,
I have several forms with subforms attached for data entry purposes. In a nutshell - this program is for an interview - where we need the respondent to give us a listing (say as to what intices them to join a study, etc.), but for some cognitive research method we need to be able to know the order they gave us the list in then we have them rank their list, thus the order may not be in the order they gave us the list. I hope that made sense. Anyway, when a form is called and loaded the subform always adds an extra record. Below is a screen shot of what I am talking about:

So, not only do I get an extra record, but when I am entering data and get down to the new record line, the cursor will jump up to the top of the column. What can I do to prevent the extra record and also the cursor skip? I am thinking if I resolve one issue the other will fall into place.

Thanks,

Scot

 

[RuralGuy]

Well Scot that could be just about anywhere. Can you post where you think you are adding the record? When you bring up the SubForm by itself does it add 1 or 2 records?

 

[MsfStl]

I'm sorry RG, I'm not sure I understand what you're asking with regards to: "Can you post where you think you are adding the record?"

Also, if I open the subform by itself, from the "Access Forms Menu" it doesn't add any records, it just lists all the records within the associated table. The filter is associated with the Parent form. I'm not sure, is that what you were asking?

 

[RuralGuy]

Yes, thanks. Could you post the filter? How is [RespID] getting set to anything? Are you doing any "SetFocus" to the SubForm in your MainForm?

 

[MsfStl]

The filter is the same as previously :

stDocName = "Sect1FL1"
stLinkCriteria = "[RespID]=" & RID
DoCmd.OpenForm stDocName, , , stLinkCriteria

However, IF I remove the stLinkCriteria, then the subform opens to the first record without regards to the Respondent ID and then overwrites the first record! I.E., if the first record ID is 1111111 then it would overwrite that to 3333333 (or whatever the current ID number is).

Now additonally I have two other snippets that are playing with the record, I think the second may be my culprit, or at least bad coding on my behalf:
1)

Public Sub Refresh1a()
Call rstbOpen
rstb.Find "RespID = " & RID
'If No record = RID Then populate - Will create error if not in INFO1 table
If rstb.EOF Or rstb.BOF Then
With rstb
.AddNew
.Fields("RespID") = RID
.Update
End With
Else

End If
Call rstbClose

End Sub

And 2)

Private Sub Form_Activate()
stDocName = Me.Form.Name
Call Refresh1a

Me.RespID = RID

End Sub

I think the "Me.RespID = RID" was what was creating an additional record, if nothing else, I believe it is needless code. However, I am pretty sure you can probably see other bad things as well. So let me have it, cause I need to straighten this spaghetti works out!

 

[RuralGuy]

To start, put Option Explicit as the second line of your code page. That will make your code barf in a number of places when you try to compile. You need to explicitly declare your variables in each subroutine. After you fix all of the compile errors put an "Exit Sub" as the second line of both of these SubRoutines. Then we'll see what happens. There is a much better method of getting the RID to the SubForm. Having rstbOpen and rstbClose SubRoutines is not a particularly good idea. You'll find the compiler will no longer like them. I doubt you will need them anyway. Let's see what we have after these changes. You have pinpointed where the two new records are coming from.

 

[MsfStl]

Ok, I'll play around with it and get back with what I find. Thanks for the heads up.

While I am thinking about it - I'll give you my thought process for how I went about developing this and maybe you can explain why I shouldn't look to do it this way.
1-Modular - I call the open and close routines throughout the program when I need to lookup specific values or update specific tables. The latter because earlier I couldn't get the form to save properly. Also, by setting it up this way I used a standardized single method of opening a recordset as well as closing it.

2-Global Variables - in one of my Modules I create my Global variables such as RID that I should be able to call throughout the program

3-Option Explicit - that is one thing I have done from the beginning. Personally, I think Access should have that set as default and if someone doesn't want then they can remove it.

 

[MsfStl]

Ok - I put Exit Sub as the second line on both the Refresh1a and the Form Activate subroutines. Now when the form opens it opens with an ID of 0 where it should be '4444444'.

My thinking is that I need the Refresh1a to populate the table first. Because it is not happening without it for some reason.

 

[RuralGuy]

I am a huge fan of modularity; so much so that I rarely, if ever use a GlogalVariable. If a SubRoutine needs some outside information then I pass it as a parameter. That way every SubRoutine or Function is easier to read. If a Form needs outside information then I pass it in the OpenArgs parameter and decode it in the Open or Load event of the Form. Even though Access allows it, I rarely get or set values going through the Forms collection. Just a personal preference of mine. Once you get used to coding in that fashion you would be surprised how easy it is to achieve. It also helps a couple of years later when you (or maybe someone else) tries to understand how the code is working.

You might be surprised how seldom you need to explicitly open and close tables in Access if you take full advantage of the built-in features. Generally if you bind forms to queries and bind SubForms to queries and set up the Link Master/Child Fields correctly, everything you want to happen just happens. Adding the ForeignKey field to a new SubForm record can be accomplished much neater using the form interface rather than opening the RecordSet behind the scenes. The SubForm RecordSet is already open by the SubForm and available for you to do as you please. It is just a matter of getting used to the syntax to "get there from here" so to speak.

 

[RuralGuy]

Good! We will get the correct value in there in a bit. Where is the zero (0) coming from? Is it a default of the field or a control on your form? Come to think of it, where is the '4444444' coming from and how is it maintained?

 

[MsfStl]

The 4444444 was the Respondent ID I was using for that particular test run. And I am glad you asked about the zero, I didn't think I had any default values or I thought I had at least removed them, but I had a zero for default on the underlying table. I have removed that default, but now I get a blank form when go to it.

 

[RuralGuy]

At least now it is behaving and we seem to be in charge again. I know that the RID is a GlobalVariable but how is it set and maintained? Hang in there Scot, we're almost there.

 

[RuralGuy]

I just looked at the first post again. Do you just fill in that TextBox at the top of the Form with the ID?

 

[MsfStl]

Supposedely it is bound with the form (and therefore to the table) for the field RespID. I double checked the properties and it IS linked to the RespID. I was surprised to find that it didn't autofill based upon the DoCmd - stLinkCriteria. Which is were and why I began all of this obtuse coding.

 

[RuralGuy]

OK Scot, this could be the final questions. What is the name of the TextBox where the "Respondent ID" is keyed in on the MainForm and also on the SubForm? If the TextBox is named "Respondent ID" please change it to txtRespondentID. It is ok for the MainForm and the SubForm to have a TextBox with the same name. If I haven't said so yet, spaces or special characters (or reserved words) in *any* name will cause you unexpected grief eventually. It is not a matter of if but when! Better to use CamelFontNames or Under_Score_Names.

 

[MsfStl]

Hey RG - What about:
DoCmd.ApplyFilter ,"RespID = " & RID ?

I am trying it on 'Form Activate' so far it seems to work, I had not heard of this method before, so I thought I would give it a shot.

Any reason why it shouldn't work?

 

[MsfStl]

The name for both the form and subform for Respondent ID is RespID, which in all honesty is the same as the field name. I've learned the hard way about having a space in any name whether it be a field or a control. Do you think there might be some ambiguity by using the same name for the field and the control?

 

[RuralGuy]

Access can get confused even though it is the default name it gives to controls. I preface all of my controls with the type of control: txtTextBox, cboComboBox, etc.

NOW, set your Link Master/Child Fields to both txtRespID (after renaming the TextBoxes of course). Please, do NOT apply a filter anywhere!

In the BeforeInsert event of the SubForm put the following:

[RespID] = Me.Parent.txtRespID

 

[MsfStl]

Unfortunately, they are still blank. I will spend some time this evening changing the control names to an acceptable convention and then hopefully, tomorrow, something may fly my way. I can not express how grateful I am for your assisstance on this. As you can tell this has befuddled me for some time.

Scot

 

[RuralGuy]

Are you telling me that when you key in an existing RespID in the RespID TextBox on the main form that the subform doesn't just show the records you keyed in earlier?