How do I use a list box to update a record in a table

[Guest]

I have just started to learn how to program, so I am very limited in this
area.

I have created a form bound to a query. I have created a list box with
specific columns from the query. The first column is bound. The list box
automatically lists specific records based on criteria setup in the query. I
have two fields for each record that need to be updated, which is done
through text boxes. The requirement is to use the text boxes to enter the
information into the rows, update the information in the table using command
buttons, and after it is updated to automatically delete the row and move to
the next row, and so-on, until they are all updated. If anyone has the time
to assist me with this complex requirement it would be most appreciated.

Thanks for your help!
JEggert

 

[John Vinson]

I have just started to learn how to program, so I am very limited in this
area.

I have created a form bound to a query. I have created a list box with
specific columns from the query. The first column is bound. The list box
automatically lists specific records based on criteria setup in the query. I
have two fields for each record that need to be updated, which is done
through text boxes. The requirement is to use the text boxes to enter the
information into the rows, update the information in the table using command
buttons, and after it is updated to automatically delete the row and move to
the next row, and so-on, until they are all updated. If anyone has the time
to assist me with this complex requirement it would be most appreciated.

Thanks for your help!
JEggert

While a Listbox could be programmed to do this - with some difficulty
- that is NOT a typical manner of using a listbox!

Ordinarily, a Listbox is used to select one row from a query - the
listbox's RowSource property, usually a query, sometimes a table; and
to store one field from that record into the Listbox's "Bound Column".
It would be unusual to base a Listbox on the same query as the Form
upon which you find the listbox.

Similarly, you're going the long way around by using VBA code (or
macros) in command buttons to update a table. Have you intentionally
*chosen* to do it this difficult way, instead of the much simpler
method of using a bound Form?

Finally, it *SOUNDS* like you want to update a row in your Table with
new data, and then delete the freshly updated row. Obviously this is
not your intent (it would be rather futile!) but I guess I don't
understand what your intent IS.

I'm *guessing* that you might have better luck by using a Subform (or
even a main form) which displays only those records which have not yet
been updated. Upon updating (using some criteria which I'm not sure
that I understand) the record would be saved to the table, but would
no longer be displayed on the (sub)Form, so only those records which
need updating would be visible to the user. Is this an accurate
summary of the desired goal?

John W. Vinson[MVP]

 

[Guest]

Your heading in the right direction. The database I have created will be used
by about 10 users, and some of these will only have access to one or two
forms, not the entire database.

I have specific documents that have to go through a declassification
process. When a document reaches a certain date then I have to go back into
the database and fill in the date the docuemnt was declassifiied and what
level of declassification. Others will be trained to do this process, so I
was trying to make it as simple as possible for them. The form automatically
lists rows of records for documents that have come up for declassification.
The two text boxes are used to update the information, which is to be
completed by pressing a command button. The intent was for those data entry
clerks to visually see the documents in the list box and as they updated them
the row would automatically be removed and ready for the next record in the
list.

However, I realized that this was not going to be simple, so I merely
created a form attached to a query and used the gotorecordfirst and
gotorecordnext command buttons with a text box that shows a numerical count
of documents to be down graded. The information for each record shows up in
the text boxes with two text boxes blank to update the information. this has
been working fine, just not as visual as I would like. If you have any
suggestions on how to accomplish the task I would appreciate it. Maybe after
this project goes into production I can go back and revise the form with a
list box. I hope this information helps you understand my request.

 

[John Vinson]

Did the suggestions in your other thread get your form working? I've
heard no response so I don't know if you're satisfied, waiting, or
giving up in frustration...

John W. Vinson[MVP]

 

[Guest]

You are correct about going the long way. My intent was to make this form
visual and easy to use for the data entry clerks. I made a couple of mistakes
going this direction. First, I only have one table for the database, and
since I was trying to accomplish two separate functions in the same table it
was causing a record lock. For almost three weeks I have been fighting a
sinus infection and the drugs have not made it easy to think through things
clearly. If I had two different tables attached with primary and foreign keys
I could have created a subform in the main form, which would have worked. I
do appreciate your help.