How to set a value into a field in a table underlying a form

[Petrol]

This one looks simple enough, but I've been struggling with it all day and just can't get it ...

I have a form in which the user can select a record from an underlying table, by using combo box with the wizard's "Find a record on my form based on the value ...". It generates the SearchForRecord macro and locates the required record OK. So far so good.

Now all I want to do is set the value of a Yes/No field in that record to Yes. I have a command button (but have tried toggle button and text box controls) which should set the field then close the form and open a report. The close form and open report work OK, but how to set the field value?

Peter

[PhilOfWalton]

Assuming there is a Yes/No field in the underlying table, bind it to a check box (tick).

Then run your code either OnClick which will close the form & open your report, or more likely on the AfterUpdate having checked that the result is True.

Phil

[Petrol]

This gives me the same result as the toggle button - "Update or CancelUpdate without AddNew or Edit". I tried putting Walks.Edit BeforeUpdate and Walks.Update AfterUpdate, but I don't really know how or where to put them to make it work.

[Petrol]

For aesthetic reasons I would prefer to use something that looks like a button that matches the other pop-up forms in the series - Make your selection the press Go".
[IMG]C:\Users\Peter\Pictures\Picture1.png[/IMG]

[Petrol]

oops - I thought that would upload the screenshot!

[Petrol]

Here is the screenshot (I hope). I would like the green "Go" to trigger the action - store the Yes/No field, close the form and open the report. That's what happens in several other forms in this series.Picture1.jpg

[PhilOfWalton]

It would appear from yours screenshot that you are trying to select men or women from your tables. This seems to contradict your original post of "select a record" - singular.

Have you a field for sex?
Is the report supposed to all the male participants or all the female participants?

Ca't really help unless both of us are sure what is wanted, and I certainly am not

Phil

[Petrol]

No, the "Walks" are not physical walks at all, they are spiritual retreats and some are for men only and some are for women only. The combo box selects and offers a choice between the next men's walk and the next women's walk - i.e. the user just has a choice between two, and by the time we get to the Go button it's only necessary to set the yes/no field for the one chosen.
So posting the picture was perhaps misleading. I considered changing the text in the form for the purpose of the illustration to "Would you like the list for Group A or Group B?", and obviously should have done so. But the requirement expressed in the OP was correct.

[PhilOfWalton]

Ah, Petrol. I asked 2 questions. Don't really think you have answered either

Phil

[Petrol]

Yes, I have a field for sex. It is the sex of the entire Walk (i.e. of all the people on it, as per my previous post). As stated in the OP, the combo box correctly selects the right one.

Yes, the report is supposed to be all the male participants. But once the correct Walk record is selected, it points to a Walk Participation table which shows the right people.

All I really need is a way to set the yes/no field of the chosen Walk record. Everything else works fine!

[Petrol]

The Walks table contains the following fields:
WalkNumber (short text, because it's alphanumeric as previously described)
Selected (the Yes/No field I'm trying to set in this form)
Gender (M/F)
StartDate (Date)
Venue (pointer to Venues table)
... (other stuff, irrelevant for present purposes)

The combo box uses a select query to offer a choice between two of these, and searches for and finds the correct record in the Walks table. All I need to do then is set it's "Selected" flag. I successfully close the form and generate the report after that (by code in the AfterUpdate event of the Go button), but at present because the Selected flag isn't set it generates a report for the wrong Walk!

[PhilOfWalton]

So the Combo Box is ether going going to give "M" or "F"

Let's assume that the combo box is called CboWalks

On Click event of you "Go" button try

Dim MyDb as Database
Dim WalkSet as RecordSet
Dim SQLStg as String"
 
SQLStg = "SELECT TblWalks.* FROM TblWalks;"
 
Set MyDb = CurrentDb
Set WalksSet = Mydb.OpenRecordset(SQLStg)
With WalkSet
    Do until .EOF
        .Edit
        If !Sex = CboWalks then
            !Selected = True
        Else
            !Selected = False
        End If
        .Update
        .MoveNext
     Loop
     .Close
     Set WalkSet = Nothing
End With
 

Phil

[Petrol]

Thanks, Phil. I really appreciate your commitment to help me and others like me. I have learned heaps from your posts over the past few months.

But in all our discussion here we seem to have moved away from the original request. Your code looks great and uses things I didn't know about (e.g. ".Edit") but it seems rather more complex than I need. I think you are searching through the entire Walks table. Let me go back to my original post:
"I have a form in which the user can select a record from an underlying table, by using combo box with the wizard's "Find a record on my form based on the value ...". It generates the SearchForRecord macro and locates the required record OK. So far so good.
"Now all I want to do is set the value of a Yes/No field in that record to Yes. I have a command button (but have tried toggle button and text box controls) which should set the field then close the form and open a report. The close form and open report work OK, but how to set the field value?"
And from my last post (#11),
"The combo box uses a select query to offer a choice between two of these, and searches for and finds the correct record in the Walks table. All I need to do then is set it's "Selected" flag. I successfully close the form and generate the report after that (by code in the AfterUpdate event of the Go button), but at present because the Selected flag isn't set it generates a report for the wrong Walk!"

Can we simplify your code to simply set the Selected flag on the record I have already selected?

[PhilOfWalton]

Sorry, Petrol

I've given it my best shot, but without actually seeing the database, I can't get to grips with what you want.

Hopefully others can help

Phil

[Petrol]

OK ... thanks very much for your best shot, Phil! I hope someone can see what I'm trying to achieve. Things always look simpler to the person doing it!