Setting up View/Edit Data form?

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

part 1 - set up an unbound form with your 5 fields each as a combobox

part 2 - set up a standalone query that has all the fields you will want in
the lower part of your form; make this query's criteris come from your
form's comboboxes - - test it so that which ever combobox field is selected -
your query returns the correct records....the tricky part will probably be
when a combobox is not selected....does that imply "all"??...you will want to
look up wildcards & comboboxes on this site...

part 3- make a new form based on your stand alone query; no form or page
headers - just data

part 4- insert your new form as a subform into your original form (no
linking between parent /child) & then add a little vb to each combobox
'AfterUpdate' event that forces the subform to requery/update..

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

> > Ed- Hide quoted text -
>
> - Show quoted text -

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....

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

> > > - Show quoted text -- Hide quoted text -