IWETHEY v. 0.3.0 | TODO
1,095 registered users | 1 active user | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Ugly Access/VB problem: subform not updating
Access2k on Win2k. I have a subform linked to a main form's autonumbering ID field. When I create a new record in the main form, the subform doesn't blank out as it should. Instead, it remains linked to the last record I was in before hitting the New Record button.

I threw in some VB on mainform.current to run all the subform's re*() functions (requery, refresh, et al). Nothing happens. subform.setfocus() makes it really refresh, but this requires data exist in all the main form's not null fields. I don't want to auto-fill these with garbage data because we'll end up with garbage records in the database. I'm looking for another way to force the sub-form to refresh.

Been googling. Seen tip to reset sourceObject. This does nothing. Other tip: Reset linkmasterfield, linkchildfield. This requires data in the not null fields. Yet another tip: Set timer to run refreshes after a few ms. This also requires data in the not null fields.

After adding repaint() to subform_current, it spends several seconds repainting, slow enough to see what it does. It appears to correctly blank out the subform when creating the new record, but then links it to the last ID after giving a new value to the autonumber.

I'm all out of ideas. Help?
New I'll have to dig through some code,
Had the same problem, now to figure out how I fixed it...

But, I'll be off-line (Class in Iowa) for the rest of the week. If no answer, will dig it out on Monday. Good luck...
"All men are like grass, and all their glory is like the flowers of the field;
the grass withers and the flowers fall, but the word of the Lord stands forever."
1 Peter 1:24-25
New I checked my code
Quick and dirty method.

I added the keyfield to the subforms (hidden). Then on the After_Update field on the Master form, I have the following code (snip)

strNewKey = fldKey (form Master Form)
Hardware!fldKey = strNewKey
Positions!fldKey = strNewKey

I have two subforms, 1 called Hardware and the other called Positions. May not be elegant, but it works!

The reason, that I have found, that it is not working, is that there is no record to link to for the subforms. And yes, to the best of my knowledge, I have all the relationships set correctly along with all the linking.
"All men are like grass, and all their glory is like the flowers of the field;
the grass withers and the flowers fall, but the word of the Lord stands forever."
1 Peter 1:24-25
New Tried "Dataset.Prev(); Dataset.Next();" ?
Or whatever the corresponding syntax would be in Access... Can't swear it'll work in Access, but it does in some other dev. environments.

(If you want to be somewhat fancy, make it "Form.StopUpdates(); Dataset.Prev(); Dataset.Next(); Form.StartUpdates();"; again, translate the syntax to AccessBasic or whatever. :-)

HTH!


   [link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Resident [link|http://z.iwethey.org/forums/render/content/show?contentid=119792|zIWETHEY pilkunnussija]
New Is/are the link subfields in the subform's dataset?
This behavior usually occurs when Access can't find the subfield name in the dataset behind the subform (due to a typo perhaps?) The fields don't have to be displayed, but they must be in the subform's datasource.

Otherwise, try to get rid of any VB code that messes with the link data items. Access gets very confused when that happens.
New Update -- still not wor.. waitasec. Fixed!
I don't fully understand jbrabek's suggestion, as there is no member named "fldkey" and I've never seen it used as pseudocode parlance. I've tried re-setting linkchildfield and linkmasterfield to the field name, but this crashes the form because the main form contains several unfilled "not null" fields. For the same reason, I would not be able to move forward and back as per CRC's suggestion. I've also tried directly setting the subform's foreign key to the main form's primary key, but this steals the last form's subform data records and points them to the current form; not the result I want. I've only tried these in _current(), not _after_update(), but I don't expect much to change.

Some more detail on my situation: The main form has a button to turn editing on or off. Editing is kept off by default so users don't screw up old records. I have a setEdits() subroutine which handles turning edit mode on and changing the button to say "start editing" or "stop editing". I've tracked the problem down to the AllowEdits = True line in this function. Also, Form_current() makes some ornamental changes to the form and turns off editing mode.

Finally got it fixed as I was typing this (funny the way that works).

Here's my code. Note that setEdits() needs to be passed False *and* NewRecord to be false to turn off the form's edit mode, since you can't turn editing off on a new record:

\nPrivate Sub setEdits(okay As Boolean)\n' utility function to switch editing mode on or off\n' No other code should change AllowEdits\n\n    If (okay = False And NewRecord = False) Then\n        Call DoCmd.RunCommand(acCmdSaveRecord)\n        AllowEdits = False\n        SurveyRequest_Roads.Form.AllowEdits = False\n        SurveyRequest_Roads.Form.AllowAdditions = False\n        EditRequest.Caption = "Start Editing"\n    Else\n        AllowEdits = True\n        AllowAdditions = True\n        SurveyRequest_Roads.Form.AllowEdits = True\n        SurveyRequest_Roads.Form.AllowAdditions = True\n        EditRequest.Caption = "End Editing"\n        ' Reset projects rowsources to default\n        Project.RowSource = "SELECT Name FROM project"\n        JobNo.RowSource = "SELECT id FROM project"\n    End If\nEnd Sub\n\nPrivate Sub Form_Current()\n\n    ' Auto-fill unlinked fields\n    Call setEdits(True)     ' turn on edits first\n    Call checkTree          ' Set unlinked checkboxes\n    Call JobNo_AfterUpdate  ' Set project name\n\n    ' Hide the debugging stuff, but leave the code in\n    If (txtVerticalDatum.Visible) Then\n        Call Flip_Hidden\n        btnHidden.Visible = False\n    End If\n\n    ' Special logic for new records\n    If (NewRecord) Then\n        ' Automatically fill in some fields\n        DateRequired.Value = Date\n        ' Go to a data input field, preferably a required value\n        Call DoCmd.GoToControl("RequestedBy")\n    Else\n        Call setEdits(False) ' turn off edits\n    End If\nEnd Sub\n


The form has unlinked checkboxes and things that are there to make it look pretty. I need to turn on AllowEdits to make these work, but then need to turn it off so old records don't get accidentally modified.

The second call to setEdits() in form_current() used to be outside the if(NewRecord) branch, under the assumption that it wouldn't make a difference. After all, if NewRecord is true, then setEdits(False) would have the same result as the setEdits(True) call at the beginning of _current(), right? Wrong.

Don't ask me what happened, but the form seems to be working now. I just need to check to make sure I didn't break it somewhere else. Thanks for the suggestions that everyone offered.
New Shouldda asked...
fldkey is the name of the key field in each record (Master, hardware, positions). Basically, upon saving, I pushed the key data to each of the sub-forms. The key field on the subforms is hidden so that data cannot be typed into it.

I'm going to look at your method and see if it will work on my apps.

Glad you got it working.

Joe
"All men are like grass, and all their glory is like the flowers of the field;
the grass withers and the flowers fall, but the word of the Lord stands forever."
1 Peter 1:24-25
     Ugly Access/VB problem: subform not updating - (tangaroa) - (6)
         I'll have to dig through some code, - (jbrabeck) - (1)
             I checked my code - (jbrabeck)
         Tried "Dataset.Prev(); Dataset.Next();" ? - (CRConrad)
         Is/are the link subfields in the subform's dataset? - (scoenye)
         Update -- still not wor.. waitasec. Fixed! - (tangaroa) - (1)
             Shouldda asked... - (jbrabeck)

Powered by static electricity.
59 ms