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

0
KateShep
1/19/2010 4:06:05 AM
access.forms 6864 articles. 2 followers. Follow

14 Replies
752 Views

Similar Articles

[PageSpeed] 8

"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_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, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com 


0
Arvin
1/19/2010 5:54:22 AM
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_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
>

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

0
KateShep
1/20/2010 12:54:15 AM
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_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
>>
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Douglas
1/20/2010 1:29:59 AM
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_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
>

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

0
KateShep
1/20/2010 4:36:58 AM
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_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
>>
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201001/1
> 


0
Arvin
1/20/2010 4:27:29 PM
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

0
KateShep
1/20/2010 4:47:11 PM
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]
0
John
1/20/2010 5:55:36 PM
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/Forums.aspx/access-forms/201001/1

0
KateShep
1/21/2010 3:13:34 AM
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]
0
John
1/22/2010 4:19:44 PM
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!


John W. Vinson 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
>[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.

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

0
KateShep
1/22/2010 9:51:41 PM
On Fri, 22 Jan 2010 21:51:41 GMT, "KateShep via AccessMonster.com"
<u57574@uwe> wrote:

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

             John W. Vinson [MVP]
0
John
1/23/2010 1:13:15 AM
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




John W. Vinson wrote:
>>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!

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

0
KateShep
1/28/2010 3:48:08 AM
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..

KateShep wrote:
>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
>
>>>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
>[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!

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

0
KateShep
1/28/2010 3:53:19 AM
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

KateShep wrote:
>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]
>>>the previous entry in this table... but the *very need to do so* strongly
>>>suggests that your tables aren't properly normalized!

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

0
KateShep
2/1/2010 6:51:33 PM
Reply:

Similar Artilces:

Hyperlink to specific Form in MSAccess
I can create a link very easily to open an Access database, but can i refer to a specific form in that database instead of the default 'switchboard' ? Proficient in VBA. Thanks ... anyone ? -- widemon ----------------------------------------------------------------------- widemonk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2040 View this thread: http://www.excelforum.com/showthread.php?threadid=48343 Maybe someone in the Access newsgroups could help more. Widemonk wrote: > > I can create a link very easily to open an Access database, ...

Hyperlinks in Custom Forms
We have a need for distributing excel spreadsheets and large documents to our outside people who dialup to get their mail. I created a custom form with a hyperlink to an EXE file that will install the document into a specific folder. The problem is that this works sometimes, but most of the time it tries to open the link in IE (then I get the unable to open the page error). I want the form to automatically download (or just give me option to download) the file when the link is clicked. I thought it might be encoded in the link somewhere as a switch or something... ANY help or sugge...

Custom Outlook Form
Hi: I am currently working on creating a custom outlook task form and have the following questions: 1. When I assign the task to someone, it sends information from hidden pages within the email. Is there a way to remove or customize the message that accompanies the task request? 2. Within the actual form itself, when a user hits enter, it sends them to the next field. Is there a way to preven this from happening? 3. I read online that you could use variables and populate portions of the form with those variables. In my case, I wanted to take the data entered by the user within a proj...

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

Double Click record in cont. form and delete the record
I am trying to double click the TransID on continuous form and delete that record. Can someone help me with the code? Anne, Do you not have enough room to put a delete button on the line in your continuous form? I would strongly suggest you create a visible Delete Button on the TransID line because that is the window standard. Double clicking on something to delete it is NOT a window starndard. If I double clicked on something, I would expect to "Open" the TransID detail. I would never ever guess that double clicking it would delete it. Now having stated the...

Exporting from Access Form to Excel
Does anyone have any ideas to get information that is entered on a Access 2000 form exported to Excel? When data is entered on my form sheet, it goes into a query. I would like it to go right to an Excel file. Any answer or any help would be appreciated. Thanks! ...

Exchange 2003 Standard edition mobile SMS (text messaging)
My mobile phone accepts SMS (text messaging). Is there any way that I make exchange to notify every time that I receive a message or I even can create an email that will send a direct message to my mobile. I understand that I can activate the mobile feature of exchange but it will require the internet to be active on my mobile costing me more money. We have Exchange 2003 Standard edition Thank you. SMS was used by Exchange ActiveSync to initiate ActiveSync in Exchange Server 2003 before SP2. The actual delivery of mail would still require a data connection on your mobile device - t...

Sequence Not populated for the MO Numbers during Data Collection
When data collection is done for the MO Numbers by using the Data Collection Window under Manufaturing, the Sequnce look up does not show the whole list, it only shows NOTES, for the user to select and enter the necessary data. why does this happen in the case of some MO Numbers and in the others it shows all the sequnces as NOTES,MAKE READY, PRESS RUN, WASH UP, MAINTENANCE, REPAIRS, REWIND/INSPECTION, etc. Any information would be much appreciated -- Thanks AshaM It should show all of the router steps. Are some of your steps possibly marked to backflush labor? These steps would not...

how do i bring a visual basic form to the spread sheet?
Hi, I have created a form using teh developer tab in excel in vb. The problem is anytime I want to access the form i need to go to the developer tab, click on vb and run it frorm there. What I would like to do is to bring the form to the spreadsheet so that I can run it directly from there. or better yet, be able to run the form independently of the spreadhseet. is that a possibility? Thanks a lot for your help, Maryam add a button to the spreadsheet and attach the code UserForm1.show 'or whatever the form name is You will not be able to run the form independant of ...

how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow...

Autoplay for Pictures Not Populated
I inserted my XD card in to my card reader today and the autoplay pop up window asking what I wanted Windows to do was blank. For Video, Music and Mixed content the options are there but not for Pictures - it is just blank. I have tried running autofix.exe but this has not fixed it. Any ideas? Is this a Microsoft problem or has something else caused this? It worked fine until today. Thanks. Peachypumpkin wrote: > I inserted my XD card in to my card reader today and the autoplay > pop up window asking what I wanted Windows to do was blank. For > Video, Music and Mi...

Customizing forms 07-02-04
OK - newbie question here. We've modified a couple of forms, and the changes show up fine when we are in the customization screens, but they never show up when the users are doing data entry. What are we missing? Fred Did you do Deployment Manager | All Tasks | Publish Customization ... ? >-----Original Message----- >OK - newbie question here. We've modified a couple of >forms, and the changes show up fine when we are in the >customization screens, but they never show up when the >users are doing data entry. What are we missing? > >Fred >. > Yep - I...

Convert a form to a split form?
After creating a subform in single form view, I realized the users would sometimes like to see all of the records at once, though the single form allows much better visiblity to the data. Is there a simple way to convert it to a Split Form, or will I have to create a new split form and move all of the custom designed UI to the new form? I tried changing the Default View on my original form to Split Form, but this did not bring up the datasheet view - is there another setting I need to change? Mich ...

Customising forms does not work
I can customise the form, I see the changes even when going to the customisation later. In the actual working env. I do not see the changes, I have tried everything, save, create form etc. but nothing works. Any suggestions?? Publish the form from deployment manager and issue an IISRESET. Matt Parks ---------------------------------------- ---------------------------------------- On Wed, 7 Apr 2004 00:53:08 -0700, "Vignir" <anonymous@discussions.microsoft.com> wrote: I can customise the form, I see the changes even when going to the customisation later. In the actual w...

Populating a field
I want to populate a description field based on a par number that is entered in the field before it. In other owrds, you enter a part number and presto, in the next field, the description is entered for you. Can you help?? Thanx Tom On Thu, 6 Dec 2007 08:48:01 -0800, Tom <Tom@discussions.microsoft.com> wrote: >I want to populate a description field based on a par number that is entered >in the field before it. In other owrds, you enter a part number and presto, >in the next field, the description is entered for you. > >Can you help?? > >Thanx >Tom If yo...

printing forms
Hi, i have this table in one sheet Name Surname Adress Tel.No. E-mail Gender Paul Gaultier Baker st 9995 paul@g.com M John Smith Rose st. 5556 john@smith.com F And in another sheet I have couple of forms (application forms, questionnaires) Applc. form. No.1 Name: Surname: Adress: E-mail: Gender: My wish is, to have buttons (or functions) next to each name, that will print each form with data of this person put in right boxes of the forms. Name Surname Adress Tel.No. E-mail Gender Print Form Paul Gaultier ...

2007
A client installed Access 2007 yesterday and now her form command buttons are not working. We have run a compile on the application code, with no errors. Checked the referenced libraries, nothing missing. She mentioned the form has a Filter OnLoad property that is not on my 2003 form properties. One command button no longer working builds a simple form filter string as follows: me.filter = filterstring me.filteron=true There are other command buttons on the form that run very simple code such as Docmd.OpenForm - nothing is working. I've reviewed the "Whats new in Access 2007&quo...

Blank field in form
I have a query that gets some criteria (City and State) from a form. I have the State field set up as a ComboBox connected to a local table I created. If a user was to type in a City (i.e. "Springfield") and then leave the State field empty, then the query would have no results. How do I make the query so that if one of the fields on the form is left empty it will run for all possibilities (i.e. look for all Springfields in any state)? It would help tremendously if you had provided the SQL view of your query. Without that I can suggest you might use a where clause like...

City and State Populated by Zip Code
Does anyone know of a way to load a program that when you type in the Zip Code the City and State are automatically populated? I have seen a MSCRM add-in from a company called QAS (http://www.qas.com/). This is more than just Zip Code/City&State. It's a full address validator along with streamlined data entry. They have integrated with CRM via an ISV pop-up and it seems to work fairly smoothly. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 15 Apr 2005 11:13:56 -0700, "Morgan" <tzeoli@att.net> wr...

Calculate Running Sum on Datasheet Veiw Form
I have a Datasheet View form with the following fields: Unitprice Subtotal Tax Linetotal Grandtotal The "Tax" field is a calculated field from the "Subtotal" field: =[subtotal]*0.0775 The "Linetotal" field is also a calculated field from the "Subtotal" and "Tax" fields: =[tax]+[subtotal] I would like to have a Running Sum of the "Linetotal" field, and have it display in the "Grandtotal" field. What Expression would I use for a Running Sum for the "Grandtotal" field? I've tried =Sum([Linetotal]) ...

Forms in E-mail folders
Have created a new e-mail template for a Public Folder. When you post new mail to that folder it comes up with the correct e-mail headers but once you go into the e-mail again once it has been posted the email headers disappear. You then have to revise contents to see them. Does anyone know how to make the email headers display once it has been posted without having to revise the contents? Thanks to anyone that can solve this problem. Tracey ...

Populate Textbox
Hi, I have a table with part numbers and locations. There are multiple locations per part number. I would like to populate a listbox with a filtered list of the locations for the part number shown on my form. I'm not very good at vba and have been looking all over for code, but can't find anything suitable. Thanks You have multiple locations per part number. So first, let's get your form set up correctly. When you have a one-to-many format (one salesman, many products or one part number, many locations) you need a form and subform. The Mainform would be for...

How to get all the texts on all type controls in form?
I want to make the multi-languages program, want to get all texts of each control item in a form, then read the XML file (Control Name and Text) to write to the text of control, and have wrote some codes, but seem that it's complicated. Can anyone tell easy method or sample? thank you. ///////////////////////////////////////////////////////////////////////////////////// Public Shared Sub AllControlOnForm(ByVal FormName As Form, ByVal xPath As String, Optional ByVal ToolTipName As ToolTip = Nothing) Try Dim ReturnValue As String = GetXMLText(xPat...

Help Please: Retrieving the first populated cell value from a specific excel spreadsheet column
I need help with retrieving the first populated cell value from a specific excel spreadsheet column. This value will then be used for later parts in an broader script to mark the entry as used. The 3rd worksheet contains 3 columns. I need this code to go through all the cells in the 2nd column, in order from top to bottom, and have it return the first value found. In this example, it should return 55553. Phone Free Used 55550 55550 55551 55551 55552 55552 55553 55553 55554 55554 55555 55555 ...........

Inventory Item List Not Populating, Generating SQL Errors
When any user tries to view the Items list under the Inventory category in the sidebar, the following warning appears: [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'ATYALLOC', table 'tempdb.dbo.##2411735'; column does not allow nulls. INSERT fails. After clicking ok, the list never populates. All of our other lists are working correctly, except for this one. What can be done to enable this list's functionality? What version of GP? What Service Pack or build number? Did this feature ever worked before? Did you just noticed thi...