Use specific record in continuous form for event?

Niniel

Registered User.
Local time
Today, 15:21
Joined
Sep 28, 2006
Messages
191
Hello,

I have a continuous subform that shows 2 records consisting of a text field and a yes/no field.
I was wondering if it was possible to take the state of one particular of the yes/no fields to trigger an event. Specifically, when one of the questions is checked [true] then I would like to make visible another subform on the main form.

Can this be done, and if so, how, or should I just leave my subform visible at all times?

Thank you.
 
one of the items in the subform will be the current record for the subform, so you can use the current event in the subform to make processing decisions.

so in the current event you can do something

me.parent.forms.[othersubform].visible = testyesno (your yesno field)

the reference syntax may not be quite right, but basically you can use the other subform via the parent of the current subform

look at referring to subforms or parents in access help
 
Hm, maybe I didn't explain this very well.

The first subform shows two records - a checkbox and a text field each - and both, either or no checkbox can be checked. So both could be the current record at one point, meaning - if I understand you correctly - that my subform could be made visible by clicking either checkbox, and not just the second one.

Or did I just misunderstand you?
 
i took you to have a main form, with two sub forms, but it doesnt matter. A form with continuous items can onyl have one current record. if each row of the continuous form displays a text box and a check box, then clicking the check box can produce an effect on the other subform (or any other item) by referencing through the parent property.

if the check box is BOUND to your record source, this will look ok. If the check box is NOT BOUND then clicking it will change the displayed setting for every row on the form.

Assuming the check box is bound, if you use the current event, then as you move from row to row, the other subform will display or hide as appropriate. Put an after update event on the check box to handle the effect of clicking the box.

Try it and see - the hard bot is getting the syntax right to reference the other form.
 
I'm afraid I just can't wrap my head around the idea of this "current record". I mean, how does Access determine which is the current record? And how would it know that the current record is really the record I need?
Suppose I have 10 records from a child table that are shown in a continuous subform on my main form. When something's done to the 6th record, I want to have an action performed on the main form. But only then.

I was thinking something along these lines might work:

if [Forms]![frmParentForm]![RecordID] and
tblAnswers.QuestionID(99) = true then
subform2.visible = true
else subform2.visible = false [is an else statement always required?]
end if

This is not really an attempt at coding, more of an illustration, but the point is that I believe I would have to reference the questionID.

My parent table is linked in a 1->many relationship to the Answers table via RecordID. The Answers table in turn is linked to the questions table via QuestionID.
 
if you have a main form and a sub form in general there will be a relationship between the two

eg - main form shows company depts, and sub form shows employees for a particluar dept

or main form shows customers, and sub forms shows orders for a particular customer.

if your subform shows record selectors which is appropriate in a continuous form, then one of the record selectors will be blackened, and is the current record in the subform. you can manually select a different record, and the different record then becomes the current record for the subform.

recording if then you can have either

if condition then action - all on one line , no endif required

if condition then
action
end if

or if condition then
action
else
action
end if

you can nest if then else to any level, but becomes hard to read, so instead do

select case condition
case result1: action
case result2: action
etc
case else: action - tidies up any other case not already covered
end select
 
Ok, that makes a whole lot more sense.
I happen to have the selector switched off, but I would assume that generally, the first record shown on a continuous form is the current record.
How do I go to record number 6 then?

Is there something like "set current = 6"?
 
You really need to see the record selectors and or the navigation buttons. If you can see the navi buttons you can type in 6 into the selector box, and go directly there.

HOWEVER, Access will not necessarily determine that the 6th record IT knows, is the same record as YOUR record No 6.

How do you know you specifically want to get to the 6th record (say). Aren't you just going to select a particular record when you see the results displayed.

I struggle to understand how you are trying to use this subform
 
I can't show those things because don't want people to browse the records at all while entering information. Plus, it looks better.

My application is a questionnaire-type db, and the 2 subforms I am talking about are subsets of my answers table, which pulls questions from a questions table, which in turn is linked to a categories table.

People use the main/parent form to enter some basic data, then run an append question to create a record for every question for the record they just created, which is then queried to show the questions and their answer fields.

There's a case of "If Yes, please answer additional question", which is why I would like to leave this one form hidden until a certain question is answered yes.

Does this make sense?
 
Last edited:
I had partial success:

Private Sub Answer_AfterUpdate()

If Answer = -1 And QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else: Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False

End If

End Sub

When I put this in the AfterUpdate event of the checkbox, I can indeed make my subform visible when I check the second box.

What is not working yet is updating the status as I browse through the records on the main form, or just loading the form.
I've been experimenting with putting the same code in the Current event of the main form, and of the subform, but to no avail yet.
 
It's working now.
Thank you very much, gemma, for taking all this time trying to help me. I appreciate it.

Private Sub Answer_AfterUpdate()

If Me.Answer = -1 And Me.QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
End If

If Me.Answer = 0 And Me.QuestionID = 99 Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

End Sub
_

Private Sub Form_current()

If DLookup("[Answer]", "tblActivityAnswers", "[ActivityID] = " & Forms!frmBrowseApplications![ActivityID] & " AND [QuestionID] = 99") = True Then
Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = True
Else: Forms!frmBrowseApplications!sfrmBrowseSubmissions.Visible = False
End If

End Sub
 
Last edited:
vba to move the current record

gemma-the-husky said:
You really need to see the record selectors and or the navigation buttons. If you can see the navi buttons you can type in 6 into the selector box, and go directly there.

HOWEVER, Access will not necessarily determine that the 6th record IT knows, is the same record as YOUR record No 6.

How do you know you specifically want to get to the 6th record (say). Aren't you just going to select a particular record when you see the results displayed.

I struggle to understand how you are trying to use this subform

My problem is along this thought i think. i need to use vba to move the current record pointer in a continous sub form. i can access the values of the current record (which happens to the first one) which is great but i need a way to loop through all the records in the sub form. I am hoping to be able to change the background for the text boxes.

main form shows parents
sub form shows children
a child may be a current student (i have a function that runs a query and returns true or false) and if they are i would like to change the background from white to yellow (so it stands out)

any ideas?
 
work around

i did a work around by adding a non updateable calculated field that determines the value i wanted and added a check box. but if someone knows how to change the background for a particular object in a continous form i would still be interested.

for now i have to figure out why my sub sub form does not display one important value but displays all the other ones.
 

Users who are viewing this thread

Back
Top Bottom