Setting up View/Edit Data form?

[Ed from AZ]

My ultimate goal here is to create a form to allow my users to view
data in the table and edit as required. I'm using Access 2007.

The data table is set up with five data fields (other than the
Autonumber): ItemType, ItemID, WkDate, WkType, and QtyWk. For any
unique combination of ItemType, ItemID, and WkDate, there may be
several records, each with a different WkType.

I want to be able to select which ItemType, ItemID, and WkDate, and
view all records associated with that combination in a form that will
allow me to edit that data and save the edits into the main data
table. I created a query against the data table, and am trying to
create a form that will allow me to choose the criteria, then run the
query and display the results.

The Split Form seemed like it was set up perfect - but the one created
by the Wizard won't work. It shows all the fields on the top and
everything as a datasheet across the bottom. I have to increment
through the records using the selectors and the bottom, and can't
choose which ones I want in the boxes on top.

Obviously I've not been this deep into Access before. If someone can
point me in a good direction - what I shoudl read about in the Help
files or samples available on the net - I would be very grateful.

Ed

 

[Ed from AZ]

Oh - I think I get it!

-- Combos on top to select the query parameters
-- Each Change event refreshes the query
-- Query results are displayed in the subform as a datasheet

Yah?

Question: can a user edit the data in the subform datasheet and have
that change saved in the data table? Or will that require some extra
coding?

Ed

 

[ntc]

query may or may not be editable depending on whether it joins multiple
tables and loses a 1:1 ratio between records; look up this topic on this
site in the query area.

but you will know as soon as you make that standalone query, even without
any filtering criteria, - just see if you can change a field or add a
record....

 

[Ed from AZ]

Whilst working through this at home, I "discovered" query SQL and
Recordsets. This seemed to make things easier, but it got me to
asking some questions about Recordsets that my non-working Help and my
Access For Dummies book doesn't cover - that's in this thread:
http://tinyurl.com/bcpr8y .

It dawned on me that I didn't need a whole separate form for
everything - I think!! Granted, I'm coming at this from years of
beating Excel with VBA to make things happen, and I'm not at all
familiar with Access. The SQL and Recordsets asa code solution seemed
my best bet - or at least an understandable one.

I have a Data Input form - two dependant listboxes and a textbox on
the top half, and multiple pairs of listboxes and textboxes. The user
selects ItemType and ItemID and enters a date. Then the user enters
pairs of WkType and QtyWrk data. When the Enter Data buton is
clicked, the code captures the ItemType, ItemID, and WkDate. This
combination should be unique, so I use DCount to check the main data
table for duplicates. If none are found, then the code iterates
through the pairs of list/text boxes to build INSERT statements and
write the data into the data table.

This View/Edit data form would be in case there was an existing entry;
the user could open this form and verify the exisiting data and change
it to remove the conflicts. Perhaps the existing data was enetered
against the wrong day. Or maybe they know the day's data is incorrect
and need to adjust it.

I already have the ItemType, ItemID, and WkDate captured - all that
remains is to query the data table against those values and display
the results in such a way that the user can easily edit the data and
click a button to overwrite the existing data records.

Since I also already have the list/text box pairs, I thought about
reading the Recordset values into the pairs, detect which ones got
changed, and iterate through them again with a statement that would
write the data into the record number returned in the Recordset.

This is probably a Neanderthal kludge, but it's what my limited
understanding can see as a solution at the moment. I'm more than
happy, though, to learn better ways!

Ed