473,322 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

For...Next in subform

In the 'On open' event of a main form, I need to check the values of a
nested subform (i.e. subform of a subform). I am able to pass the
first value, and even though it goes through each time, it is not
passing the new value to the code..so if the first value is what I
want, great, but if it is in record 2 thru x, it acts as if it is not
there.

BTW, I am just having the code bring up a message box to test. Once
this works, I will be enabling or disabling other fields in the main
form.

Here's the code:

Private sub Form_Current()
Dim pr as variant
Dim rs as variant

pr = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm ("ProcID")
rs =
Forms!frmchart!subChartProc!frmChartProcsubform.fo rm.RecordsetClone

For each pr in rs
if Me.subchartproc.form!frmchartprocsubform("procID") = 20 then
msgbox "bingo", vbcritical, 'bingo"
exit for
End if
next
end sub

Like I said, I get no errors, but it only works if the first record in
the subform = 20. When I step through the code, it goes through the
loop for each record in the recordset, but doesn't update the ProcID

Thanks in advance for any input.

Mar 7 '06 #1
3 3227
There could be a timing issue here.

Assuming the subform control has something in its
LinkMasterFields/LinkChildFields properties, Access will reload the subform
when the main form moves record. The form's Current fires when you move
record, so you may find that the subform has not loaded yet when this event
fires. The sub-subform will then have to reload after the subform does, so
it seems unlikely that your logic could work successfully. (If you are
actually using the Open event of the form as you stated, the situation would
be worse.)

You might be able to work around that if you performed a DLookup() or
DCount() directly on the sub-subform's table to examine the records that
will be loaded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dataeagle" <mc***@cmitsolutions.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...
In the 'On open' event of a main form, I need to check the values of a
nested subform (i.e. subform of a subform). I am able to pass the
first value, and even though it goes through each time, it is not
passing the new value to the code..so if the first value is what I
want, great, but if it is in record 2 thru x, it acts as if it is not
there.

BTW, I am just having the code bring up a message box to test. Once
this works, I will be enabling or disabling other fields in the main
form.

Here's the code:

Private sub Form_Current()
Dim pr as variant
Dim rs as variant

pr = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm ("ProcID")
rs =
Forms!frmchart!subChartProc!frmChartProcsubform.fo rm.RecordsetClone

For each pr in rs
if Me.subchartproc.form!frmchartprocsubform("procID") = 20 then
msgbox "bingo", vbcritical, 'bingo"
exit for
End if
next
end sub

Like I said, I get no errors, but it only works if the first record in
the subform = 20. When I step through the code, it goes through the
loop for each record in the recordset, but doesn't update the ProcID

Thanks in advance for any input.

Mar 7 '06 #2
Br
dataeagle wrote:
In the 'On open' event of a main form, I need to check the values of a
nested subform (i.e. subform of a subform). I am able to pass the
first value, and even though it goes through each time, it is not
passing the new value to the code..so if the first value is what I
want, great, but if it is in record 2 thru x, it acts as if it is not
there.

BTW, I am just having the code bring up a message box to test. Once
this works, I will be enabling or disabling other fields in the main
form.

Here's the code:

Private sub Form_Current()
Dim pr as variant
Dim rs as variant
Variant? No, no.

Dim pr as Long '(I assume it's a Long Integer).
Dim rs as DAO.Recordset

(assuming your are using DAO, else 'Dim rs as ADODB.Recordset')
pr = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm ("ProcID")
rs =
Forms!frmchart!subChartProc!frmChartProcsubform.fo rm.RecordsetClone

If you are referencing this form more than once create a variable to store a
pointer to it.

Dim myForm as Form
Set myForm = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm
pr = myForm("ProcID")

SET rs = pr.RecordsetClone

(you need to use SET now we're actually using a recordset)
For each pr in rs
That doesn't seem correct to me.

Why not just loop through the recordset?

rs.Movefirst
Do While Not rs.EOF
If rs("ProcID") = 20 Then 'note reference the recordsetclone, not the
form!!
Msgbox "bingo"
Exit While
End If
rs.Movenext
Loop
if Me.subchartproc.form!frmchartprocsubform("procID") = 20 then
msgbox "bingo", vbcritical, 'bingo"
exit for
End if
next
end sub

Like I said, I get no errors, but it only works if the first record in
the subform = 20. When I step through the code, it goes through the
loop for each record in the recordset, but doesn't update the ProcID

Thanks in advance for any input.


The main reson it only worked for one record is that you refrenced the form
which only has one current record that you can access (which is why you use
a copy of the recorset, ie. recordsetclone)
--
regards,

Br@dley
Mar 7 '06 #3
Thanks Bradley!
Your post was the ticket to getting this right. I did make the pr a
long and rs a DAO.recordset. However, in the end I did not need the pr
statement at all as I Set rs = myform.recordsetClone instead of just
the field. The Do While statement is something I should have thought
of and thank you for hitting me in the head with that...my wife is
usually the one who likes to do that to me.

Anyway, all is right with the world (for now), thanks again.

Mar 7 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: entellisys | last post by:
Hi there, Does anyone know the simplest way I can trap the next record event in Access 2000 on a subform? I have a main form with tab control which holds several subforms, and the subforms...
3
by: William Wisnieski | last post by:
Hello Everyone, Access 2000, I have a main unbound form with a bound datasheet subform . The subform is bound to a query that returns records based on criteria in the main form. The user...
9
by: Karl Roes | last post by:
Hi All, I would like some advice on Next / Previous record buttons. I have a main form for the client, and a continuous subform listing client transactions. If I open one of these transactions...
6
by: (Pete Cresswell) | last post by:
I *know* I've seen this before but can't remember where. Got a subform - pretty simple, actually. It's lined to a work table that contains comments related to a mutual fund. No problem...
15
by: Susan Bricker | last post by:
Greetings. I have a Mainform with a subform. The Mainform has single record format and subform has continuous form format. When the Mainform opens, I force allowadditions and allowedits to FALSE...
11
by: scsTiger | last post by:
I am using Access 2000 as the front end and MS SQL 2000 as the backend. I have a one record form that I set using something like: strSQL = "SELECT * FROM dbo_WBACCT WHERE...
2
by: gavm360 | last post by:
Hello, I have a data entry form that has a button to copy the values of fields (CASECODE,STEPDES,CBOANSWER) into the fields (CASECODE,STEPDES,ANSWER) of a subform on the page. Private Sub...
1
by: stuart | last post by:
I have a list of records in a subform that a user can either edit or delete. This is an unbound form. If the user deletes a record, I want to refresh the form, and then position the cursor on the...
3
by: Lynx101 | last post by:
I am trying to move a subform onto the next record with: DoCmd.GoToRecord acForm, "MaintainAssets_edit", acNext but when I do this I get "The object 'MaintainAssets_edit' is isn't open". I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.