Pulling information from a recordset into a form

[Rachel Garrett]

I have a subform that pulls some fields from the main form, and has
other fields that the user supplies. The results are stored in a table
(call it SubTable).

Right now, a fresh record is created each time the user goes to the
subform, fills out all required fields, and clicks Save. This is
managed with VBA/SQL.

Problem: users want to be able to go back and edit records that they
recently created. Since the index of SubTable is a superkey that
includes the date, it is easy to create a recordset that consists of
the data I want. But I don't know how to refer to it once I've got it
in the recordset.

Logically, what I'm trying to do is this:

When user opens the subform
Field 1 and Field 2 are populated from main form
SQL query creates recordset from

where Field 1 = Field 1 value
in form, Field 2 = Field 2 value in form, and date is less than 7 days
ago

 

[Rachel Garrett]

Apologies...I accidentally hit Enter too early.

I have a subform that pulls some fields from the main form, and has
other fields that the user supplies. The results are stored in a
table
(call it SubTable).

Right now, a fresh record is created each time the user goes to the
subform, fills out all required fields, and clicks Save. This is
managed with VBA/SQL.


Problem: users want to be able to go back and edit records that they
recently created. Since the index of SubTable is a superkey that
includes the date, it is easy to create a recordset that consists of
the data I want. But I don't know how to refer to it once I've got it
in the recordset.


Logically, what I'm trying to do is this:


When user opens the subform
--Field 1 and Field 2 are populated from main form
--SQL query creates recordset from

where Field 1 = Field 1
value
in form, Field 2 = Field 2 value in form, and date is less than 7
days
ago
--If the recordset is empty
-------user enters new information in blank fields
-------new record is created in [SubformTable] when user clicks "Save"
--If the recordset is not empty
******Field 3 is populated from the recordset, where the Index
(string) in the recordset = Field 1 & Field 2 & DateofRecord
******Field 4 ""
-------record is UPDATEd in [SubformTable] when the user clicks save

It's the ******** steps that I don't "get". I know how to refer to a
*field* in a recordset, but I don't know how to refer to a field in a
record that matches some criteria. I can do that in SQL, but I don't
know how to make the "result" from SQL get back into VBA so I can put
it in the form.

Many thank you's to whoever takes a stab at explaining this to me.

--Rachel Garrett