Populate Form to Edit

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

"KateShep" u57574@uwe wrote in message news:a2536ec428d9c@uwe...
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_He
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, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.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


Arvin Meyer [MVP] wrote:
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_He
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


--
Message posted via http://www.accessmonster.com

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"KateShep via AccessMonster.com" u57574@uwe wrote in message
news:a25e54c454292@uwe...
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


Arvin Meyer [MVP] wrote:
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_He
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


--
Message posted via http://www.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

Arvin Meyer [MVP] wrote:
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_He
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

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


"KateShep via AccessMonster.com" u57574@uwe wrote in message
news:a260466994041@uwe...
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

Arvin Meyer [MVP] wrote:
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_He
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

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

Arvin Meyer [MVP] wrote:
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]
Resume Exit_Here
End Sub

--
Message posted via http://www.accessmonster.com

On Wed, 20 Jan 2010 16:47:11 GMT, "KateShep via AccessMonster.com"
u57574@uwe wrote:

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

John W. Vinson [MVP]

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 wrote:
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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

On Thu, 21 Jan 2010 03:13:34 GMT, "KateShep via AccessMonster.com"
u57574@uwe wrote:

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

John W. Vinson [MVP]