List box - Altering records

[HebieBug]

Have a nice MIND BLOWING problem for anyone out there
Does anyone know how to change all records displayed in a list box.
Situation is once a record is diplayed in a list box it should automatically change a field wihin that record from NO to YES
And that's about it
Thanx

 

[RickSpr]

If your list box's Row Source is a table or updatable query, you could write code to update the record for a given row. But it's not clear what you mean by "once a record is displayed in a list box". Perhaps you mean that the record is displayed on the form when it is selected in the list box? If so, should the field in the underlying table be updated as a result of the user navigating to the record by another means, such as the navigation bar or the Find dialog?

If you mean you just want to change a column in the list box when the user has selected an item from the list, that's very different. It's also quite complicated, since you'd have to write your own list fill function to populate the list box, and I don't see any obvious reason why somebody would want to do this.
Rick Sprague

 

[HebieBug]

Hi there Rick,
What is happening at the moment is when a form loads there is a set procedure that it runs. This procedure displays a field agent and all the corrisponding clients that they have. It then faxes all the details of those clients to the field consultants fax machine.
The data that is faxed to the field agent comes from a query and then a report. The query has been setup with the criteria that if the faxed field = No. So what I was after was a situation where after the clients data is faxed then the form load code will then change the faxed field to YES so that the data is not faxed again.
A nice brain buster

 

[RickSpr]

I still have no idea what is in your list box, or where your "faxed field" is. In the table with the field agents? With the clients? What does this have to do with the list box anyway? The Form_Load procedure runs before the list box is even available to the user.

"The data that is faxed to the field agent comes from a query and then a report." How can your Form_Load procedure fax data from a report? Does it get the data from the query? Is the query the form's Record Source, or is it an unbound form and the Form_Load code opens it in an internal Recordset variable?

You talk in generalizations. How can you expect anybody to give you a specific solution? The best answer I can give you at this point is, after you've faxed the data, just open a recordset on the table with the field agents, find the record for the agent you just faxed to, set the Faxed flag to Yes, and save your update. That's too obvious to be helpful, I know, and it has nothing to do with a list box, which seemed important in your original question. But it's the most specific answer I can give based on the information you've supplied. Rick Sprague

 

[HebieBug]

That's a valid point. But the reports that are faxed to the field agent are usally not faxed one by one. They are faxed anywhere up to 10 records. Recordset would be best used if there was only one record sent at a time.
---- A little bit more detail -------------------
The list box displays all the records that are being sent to the field agents.
The form load procedure, using recordset, places the first field agents name into a text box with corrasponding details such as fax number ect..
It then opens a report based on a query (The list box data is based on that query) The query's criteria is set to match that of the field agent displayed in form. It then runs DSK (Winfax VB code) sends the fax.
The next field agents name is then placed in text box and the list box is requeryed. The procdure then sends the next lot of records to be faxed.
So as you can see it is a little bit more complicated then previously speculated.
The question is while the list box is displaying those records insteed of using recordset with the find command set to look for field agent and then creating a loop in the process. Is is possible to take the records shown and altering them in mass.
Tried to keep simple and am attempting to keep it that way because to describe how the function of this form works from start to finish in detail would require a full page of exlimation and lets face it who wants to read more then 1 paragraph.
Appricate that you are helping out

 

[RickSpr]

I gather that the form isn't meant to be a user interface at all, other than displaying the field agent and list of clients currently being faxed. That's a departure from the normal way a form is used. You might have said so.

Somehow the query is joining field agents with their clients. I'll assume this is a one-to-many relationship, though that's far from clear. And I'll assume that the tables are called FieldAgents and Clients, and the query join field in the Clients table is called FieldAgentID.

The list box holds data about clients, so I'll assume that you want to update a Yes/No field in the Clients table, which field I'll assume is called Sent. You originally seemed to be saying you wanted to update the data in the list box, but that makes no sense, because the data wouldn't be saved anywhere.

You can update all the Sent fields to Yes with a single SQL statement:
UPDATE Clients SET Sent = True WHERE FieldAgentID = (the ID of the field agent shown in the form)
You would execute this SQL statement after you finish faxing to one field agent. It would update all the clients at once.

There, now that wasn't so hard, was it? All I had to do was guess at the existence of a Clients table, guess at the existence of a Sent field in it, guess that the Sent field was what you wanted to update, and guess that the Clients table was related to a FieldAgents table via a FieldAgentID field. Of course, if you had told me this in the first place, I wouldn't have had to guess at all. Why did you think that concealing the relevant details made things simpler??? Rick Sprague

 

[HebieBug]

Loaded Microsoft DAO 3.6 object library.
Tried the code that you suggested but it come up with
Expected end of statement error message on Set.
Was there any more code that needs to be added to run this SQL code or any other reference library?

 

[RickSpr]

Sorry. Since you identify yourself as a programmer, and since your form apparently contains some non-trivial code, I thought you'd know how to run a query.

Dim db As Database
Set db = CurrentDb
db.Execute "UPDATE Clients SET Sent = True WHERE FieldAgentID = " & CStr(Me!FieldAgentID)

If FieldAgentID is not a numeric value, you'll have to change that to:
db.Execute "UPDATE Clients SET Sent = True WHERE FieldAgentID = '" & CStr(Me!FieldAgentID) & "'"
Rick Sprague

 

[HebieBug]

We can close the case on this one then..