Populate Form to Edit

[KateShep]

I am pretty new to Access 2007 (used to know 2003), and I know nothing about
VBA.

In theory, what I'm trying to do is pretty simple. We have parents of infant
research participants fill out an information form each time they visit our
research lab. We will enter the info into form "Basic Information Form 1"
under the infant's id [Infant ID]. The next time the parent visits, they will
update the form with new information (such as infant's new weight, hours in
day care, etc.). I want to auto-populate the info from the first visit's form
[Basic Information Form 1] to a new second visit form [Basic Information Form
2], so the first set of information is entered into the 2nd form and we can
change it as needed, without changing the information saved in the first
table. So Basic Form 2 will generate a new table, although most of the
information may be identical to Form 1. The tables are linked via [Infant ID]

I thought I could set each field's Default Value in Form 2 to refer to the
field from "Basic Information Form 1", but it's not working for me. I've
tried using DLookup() in the Default Value.

Any suggestions are greatly appreciated!

- Kate

 

[Arvin Meyer [MVP]]

I am pretty new to Access 2007 (used to know 2003), and I know nothing
about
VBA.

In theory, what I'm trying to do is pretty simple. We have parents of
infant
research participants fill out an information form each time they visit
our
research lab. We will enter the info into form "Basic Information Form 1"
under the infant's id [Infant ID]. The next time the parent visits, they
will
update the form with new information (such as infant's new weight, hours
in
day care, etc.). I want to auto-populate the info from the first visit's
form
[Basic Information Form 1] to a new second visit form [Basic Information
Form
2], so the first set of information is entered into the 2nd form and we
can
change it as needed, without changing the information saved in the first
table. So Basic Form 2 will generate a new table, although most of the
information may be identical to Form 1. The tables are linked via [Infant
ID]

I thought I could set each field's Default Value in Form 2 to refer to the
field from "Basic Information Form 1", but it's not working for me. I've
tried using DLookup() in the Default Value.

Any suggestions are greatly appreciated!

You need to lookup based on 2 values, the ID and the last visit date,
something like:

DLookUp("[Some Field]","[Table Name]","[Infant Id] = " & Form![Infant Id] &
" And [Visit Date]=#" & Form![Visit Date] & "#")

And you'll need to do it for each field. Alternatively (and much better if
you can get the VBA right) is to pull a recordset and set each control equal
to the value from the recordset. Here's a guesstimate of what you need. This
is entirely untested:

Sub cmdFillValues_Click()
On Error GoTo Error_Handler
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim strSQL As String

strSQL = "SELECT TOP 1 FROM tblYourTable WHERE [Infant ID] =" & Me.[Infant
ID] & " Order By [Visit Date] DESC;"
Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Me. [Field1] = ![Field1]
Me. [Field2] = ![Field2]
Me. [Field3] = ![Field3]
'etc.
End With

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
Msg Box Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[KateShep via AccessMonster.com]

Thanks, Arvin. Here's another idea I had, since I don't know anything about
VBA. Could you (or anyone else) let me know if this might work?

Form 2 will have same (blank) fields as Form 1, but we will only enter new
info into Form 2 (maybe just 5 fields out of 30 for example). Then I could
set up a query to pull all nonblank cells from Form 2 for each Infant ID, and
set a function like Iif()? to pull "blank" fields in Form 2 from the
corresponding fields in Form 1 (which will not be blank). Do you think that
would work?

Thanks,
Kate

I am pretty new to Access 2007 (used to know 2003), and I know nothing
about

[quoted text clipped - 25 lines]

Any suggestions are greatly appreciated!

You need to lookup based on 2 values, the ID and the last visit date,
something like:

DLookUp("[Some Field]","[Table Name]","[Infant Id] = " & Form![Infant Id] &
" And [Visit Date]=#" & Form![Visit Date] & "#")

And you'll need to do it for each field. Alternatively (and much better if
you can get the VBA right) is to pull a recordset and set each control equal
to the value from the recordset. Here's a guesstimate of what you need. This
is entirely untested:

Sub cmdFillValues_Click()
On Error GoTo Error_Handler
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim strSQL As String

strSQL = "SELECT TOP 1 FROM tblYourTable WHERE [Infant ID] =" & Me.[Infant
ID] & " Order By [Visit Date] DESC;"
Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Me. [Field1] = ![Field1]
Me. [Field2] = ![Field2]
Me. [Field3] = ![Field3]
'etc.
End With

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
Msg Box Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[Douglas J. Steele]

No, you can't do it using queries. You have to use VBA or macros.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, Arvin. Here's another idea I had, since I don't know anything
about
VBA. Could you (or anyone else) let me know if this might work?

Form 2 will have same (blank) fields as Form 1, but we will only enter new
info into Form 2 (maybe just 5 fields out of 30 for example). Then I could
set up a query to pull all nonblank cells from Form 2 for each Infant ID,
and
set a function like Iif()? to pull "blank" fields in Form 2 from the
corresponding fields in Form 1 (which will not be blank). Do you think
that
would work?

Thanks,
Kate

I am pretty new to Access 2007 (used to know 2003), and I know nothing
about

[quoted text clipped - 25 lines]

Any suggestions are greatly appreciated!

You need to lookup based on 2 values, the ID and the last visit date,
something like:

DLookUp("[Some Field]","[Table Name]","[Infant Id] = " & Form![Infant Id]
&
" And [Visit Date]=#" & Form![Visit Date] & "#")

And you'll need to do it for each field. Alternatively (and much better if
you can get the VBA right) is to pull a recordset and set each control
equal
to the value from the recordset. Here's a guesstimate of what you need.
This
is entirely untested:

Sub cmdFillValues_Click()
On Error GoTo Error_Handler
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim strSQL As String

strSQL = "SELECT TOP 1 FROM tblYourTable WHERE [Infant ID] =" &
Me.[Infant
ID] & " Order By [Visit Date] DESC;"
Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Me. [Field1] = ![Field1]
Me. [Field2] = ![Field2]
Me. [Field3] = ![Field3]
'etc.
End With

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
Msg Box Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[KateShep via AccessMonster.com]

Can you explain why I need to look up based on two values, as you suggested?
I have the Infant I'd field indexed with No Duplicates, so shouldn't it be
able to lookup based on one field?

Thanks,
Kate

I am pretty new to Access 2007 (used to know 2003), and I know nothing
about

[quoted text clipped - 25 lines]

Any suggestions are greatly appreciated!

You need to lookup based on 2 values, the ID and the last visit date,
something like:

DLookUp("[Some Field]","[Table Name]","[Infant Id] = " & Form![Infant Id] &
" And [Visit Date]=#" & Form![Visit Date] & "#")

And you'll need to do it for each field. Alternatively (and much better if
you can get the VBA right) is to pull a recordset and set each control equal
to the value from the recordset. Here's a guesstimate of what you need. This
is entirely untested:

Sub cmdFillValues_Click()
On Error GoTo Error_Handler
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim strSQL As String

strSQL = "SELECT TOP 1 FROM tblYourTable WHERE [Infant ID] =" & Me.[Infant
ID] & " Order By [Visit Date] DESC;"
Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Me. [Field1] = ![Field1]
Me. [Field2] = ![Field2]
Me. [Field3] = ![Field3]
'etc.
End With

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
Msg Box Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[Arvin Meyer [MVP]]

Because, you want to get the values from the latest visit. If there is a
guarantee of 1 record and you are just editing that record, the InfantID
will be sufficient.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Can you explain why I need to look up based on two values, as you
suggested?
I have the Infant I'd field indexed with No Duplicates, so shouldn't it be
able to lookup based on one field?

Thanks,
Kate

I am pretty new to Access 2007 (used to know 2003), and I know nothing
about

[quoted text clipped - 25 lines]

Any suggestions are greatly appreciated!

You need to lookup based on 2 values, the ID and the last visit date,
something like:

DLookUp("[Some Field]","[Table Name]","[Infant Id] = " & Form![Infant Id]
&
" And [Visit Date]=#" & Form![Visit Date] & "#")

And you'll need to do it for each field. Alternatively (and much better if
you can get the VBA right) is to pull a recordset and set each control
equal
to the value from the recordset. Here's a guesstimate of what you need.
This
is entirely untested:

Sub cmdFillValues_Click()
On Error GoTo Error_Handler
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim strSQL As String

strSQL = "SELECT TOP 1 FROM tblYourTable WHERE [Infant ID] =" &
Me.[Infant
ID] & " Order By [Visit Date] DESC;"
Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
Me. [Field1] = ![Field1]
Me. [Field2] = ![Field2]
Me. [Field3] = ![Field3]
'etc.
End With

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
Msg Box Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub

 

[KateShep via AccessMonster.com]

I see. The way I had it set up was separate forms for each visit, so Form 1
is based on Table 1, and will always only be filled out once for each Infant
ID (which is indexed).

Anyway, I was able to figure it out with a Query afterall. It will probably
be more work in the long run to set it up, but it's easier for me right now
than learning Macros/VBA.

I have a Select Query set up with expressions for each field I want to show:

Birthdate: IIf([basic information 2].[Birthdate] Is Null,[basic information].
[birthdate],[basic information 2].[birthdate])

It seems to work so far, where it pulls Form 2's blank fields from Form 1...
This will save us lots of data entry!

Thanks so much for all of your suggestions!

- Kate

Because, you want to get the values from the latest visit. If there is a
guarantee of 1 record and you are just editing that record, the InfantID
will be sufficient.

Can you explain why I need to look up based on two values, as you
suggested?

[quoted text clipped - 54 lines]

 

[John W. Vinson]

I see. The way I had it set up was separate forms for each visit, so Form 1
is based on Table 1, and will always only be filled out once for each Infant
ID (which is indexed).

WHOAAA HERE!!!!

so if an infant is involved in eleven visits, you will have eleven forms?
eleven TABLES?

If so you're *way way off track*!

Or am I misunderstanding?

 

[KateShep via AccessMonster.com]

I see your point, John! Most infants return 4 times, maybe 5 total. (We run
studies with 4 month olds, 6, 8, and 9 or 10 months). I could see this as
doable, BUT, after thinking this through more and considering how I wanted to
access the data in the table, I think I'm going to try to use Arvin's
original suggestion about the VBA code. I just have a few of (naive)
questions.

I'll stick to one table for the Basic Info Form (as opposed to a separate
form for each visit).

1. Where do I put the VBA code? (On Current, On Load, On Click...etc or am i
WAY off still??

2. When you say "recordset", do you mean one instance of [Infant ID] and one
[Visit Date]? And if so, where/how do I tell the form which Infant ID/Date to
use (from the line of code: "SELECT TOP 1 FROM tblYourTable WHERE [Infant ID]
=" & Me.[Infant
ID] & " Order By [Visit Date] DESC;"

3. Is this getting too complicated if when entering Infant's visit 2, it
pulls default values from visit 1, but when entering visit 3, it pulls
default values from visit 2? I guess if I know which Visit Date to request,
then we can enter the most recent visit.

4. I guess I'm not understanding when during the data entry process the form
will populate with the previously entered values. Is it after we type the
current Infant ID and Visit Date, or will a box pop up before typing those so
we can enter the Infant ID and previous Visit Date...or..?

Thanks..

- Kate

 

[John W. Vinson]

I see your point, John! Most infants return 4 times, maybe 5 total. (We run
studies with 4 month olds, 6, 8, and 9 or 10 months). I could see this as
doable, BUT, after thinking this through more and considering how I wanted to
access the data in the table, I think I'm going to try to use Arvin's
original suggestion about the VBA code. I just have a few of (naive)
questions.

I'll stick to one table for the Basic Info Form (as opposed to a separate
form for each visit).

1. Where do I put the VBA code? (On Current, On Load, On Click...etc or am i
WAY off still??

You probably don't need ANY CODE AT ALL.

If you have a Mainform with information about the infant, and a subform based
on a table of Visits, you would need to only *navigate* to the record for that
infant. You don't need to "populate the form" in the sense you're thinking!
Remember, the form is just a movable window displaying data stored in the
table. You can certainly add a very simple combo box to find the record for a
particular infant, and display the data in that record. The Subform would then
let you add information for the first visit... or the second... or the fourth
or whatevever.

 

[KateShep via AccessMonster.com]

Well what we are trying to do is avoid typing in up to 30 or so fields of
repetitive data for the infant's 2nd, 3rd, etc visits. The original questin
was "How can I autopopulate fields with data from 1st visit so we can edit
any new information for 2nd visit, which may only be 5 fields of new info.
But we'd still want each visit to have a full complete form to run statistics
from depebding on the visit date/studies run.

I hope this makes sense!

I see your point, John! Most infants return 4 times, maybe 5 total. (We run
studies with 4 month olds, 6, 8, and 9 or 10 months). I could see this as

[quoted text clipped - 8 lines]

1. Where do I put the VBA code? (On Current, On Load, On Click...etc or am i
WAY off still??

You probably don't need ANY CODE AT ALL.

If you have a Mainform with information about the infant, and a subform based
on a table of Visits, you would need to only *navigate* to the record for that
infant. You don't need to "populate the form" in the sense you're thinking!
Remember, the form is just a movable window displaying data stored in the
table. You can certainly add a very simple combo box to find the record for a
particular infant, and display the data in that record. The Subform would then
let you add information for the first visit... or the second... or the fourth
or whatevever.

 

[John W. Vinson]

Well what we are trying to do is avoid typing in up to 30 or so fields of
repetitive data for the infant's 2nd, 3rd, etc visits. The original questin
was "How can I autopopulate fields with data from 1st visit so we can edit
any new information for 2nd visit, which may only be 5 fields of new info.
But we'd still want each visit to have a full complete form to run statistics
from depebding on the visit date/studies run.

Static information pertaining to the infant should NOT BE in the Visits table;
it should be in the "one" side table.

It would really help if you would post a bit more description of the fields in
your tables and their relationships.

You can certainly write code to have any fields that you want defaulting to
the previous entry in this table... but the *very need to do so* strongly
suggests that your tables aren't properly normalized!

 

[KateShep via AccessMonster.com]

The problem is that not much of the info is static, so I'm trying to think of
a solution to avoid entering "potentially" static data, with the option of
changing the data and saving as a new and separate record in the table. All I
really want is to set the default values to the first visit data.

These are some of the fields:
Static fields:
Gestation weeks, Birthdate, Birth order, Birth weight, Birth length, Birth
place, Birth complications, Race, Ethnicity, etc.

(Potentially) changing fields:
Current age, current weight, current height, parents' marital status, parent
education level, work status, caregiver information, caregiver hours, current
medications, history of ear infections, frequency of reading books, watching
tv, etc.

I can see how a Main Form and a subform could help, but I'd still prefer to
have one form that is auto-filled in with default information from the first
visit.

Any suggestions on how to get that going?

Thanks,
Kate

 

[KateShep via AccessMonster.com]

Regarding the relationships between the fields/tables... An infant who comes
at 4 months of age will participate in a study ("4-month study"), and "4-
month study" results (looking time in seconds to various stimuli) will be
imported into a table. Study results table will be linked to this "visits"
(or Basic Information) table so we can examine whether results are related to
variables in the table, such as infant's current height or medications. The
same infant will come back at 6 months of age to participate in the "6-month
study", and the visits table will be updated with their more current
information (here is where I'm trying to default values to the first 4-month
visit date), and the new study results will be linked to the new visit
information record to see, again, if there are relationships between the new
results and their new height, weight, etc.

I hope this makes sense..

The problem is that not much of the info is static, so I'm trying to think of
a solution to avoid entering "potentially" static data, with the option of
changing the data and saving as a new and separate record in the table. All I
really want is to set the default values to the first visit data.

These are some of the fields:
Static fields:
Gestation weeks, Birthdate, Birth order, Birth weight, Birth length, Birth
place, Birth complications, Race, Ethnicity, etc.

(Potentially) changing fields:
Current age, current weight, current height, parents' marital status, parent
education level, work status, caregiver information, caregiver hours, current
medications, history of ear infections, frequency of reading books, watching
tv, etc.

I can see how a Main Form and a subform could help, but I'd still prefer to
have one form that is auto-filled in with default information from the first
visit.

Any suggestions on how to get that going?

Thanks,
Kate
[quoted text clipped - 12 lines]

the previous entry in this table... but the *very need to do so* strongly
suggests that your tables aren't properly normalized!

 

[KateShep via AccessMonster.com]

Update: I incorporated suggestions made above, such as using subforms for
changing information and linking by infant ID and Received Date. I also found
code for "duplicate last record" into form controls at
http://www.allenbrowne.com/ser-24.html


I'm now trying to figure out how to duplicate from last record in a subform
when the LinkChildFields don't match the current record. ie, the Infant ID is
the same for each record, but the Received Date is different, so the code
assumes there is no previous record to duplicate from.

If anyone has any suggestions, I'd really appreciate it!

Thanks,
Kate

Regarding the relationships between the fields/tables... An infant who comes
at 4 months of age will participate in a study ("4-month study"), and "4-
month study" results (looking time in seconds to various stimuli) will be
imported into a table. Study results table will be linked to this "visits"
(or Basic Information) table so we can examine whether results are related to
variables in the table, such as infant's current height or medications. The
same infant will come back at 6 months of age to participate in the "6-month
study", and the visits table will be updated with their more current
information (here is where I'm trying to default values to the first 4-month
visit date), and the new study results will be linked to the new visit
information record to see, again, if there are relationships between the new
results and their new height, weight, etc.

I hope this makes sense..

The problem is that not much of the info is static, so I'm trying to think of
a solution to avoid entering "potentially" static data, with the option of

[quoted text clipped - 26 lines]