Data Entry through a macro

[desireemm]

Hi all I have a question can you have a macro insert data into tabls for you,
for faster data entry? See I was thinking of creating a form with the
necessary fields that need to be populated. And what the user would do is
look up the record they want to populate then hit the macro button and have
it insert the data into those fields. is there a way I can do that, because
if a user has to insert the same data over and over again but for different
records then this would make it faster for them. Can anyone tell me how I
can do that. I'm not a programmer so i was hoping for a macro or something??

 

[bhicks11 via AccessMonster.com]

Hi Desiree

Yes you can do this. Where would they be getting the data from? The last
record or a table? In a macro you would use the setvalue function. I use
DLOOKUP to populate several fields in VBA with something like this:

If IsNull(STREET) Then
Me.LNAME = DLookup("[LNAME]", "[TEST1]", "([SLNUM] = '" & Me.SLNUM
& "') And (

• = '" & Me.List & "')")
  Me.FNAME = DLookup("[FNAME]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM &
  "') And (
  • = '" & Me.List & "')")
    Me.COMPANY = DLookup("[OFFICEORG]", "[TEST1]", "([SLNUM]= '" & Me.
    SLNUM & "') And (
    • = '" & Me.List & "')")
      Me.STREET = DLookup("[ADDRESS1]", "[TEST1]", "([SLNUM]= '" & Me.
      SLNUM & "') And (
      • = '" & Me.List & "')")
        Me.ADDRESS2 = DLookup("[ADDRESS2]", "[TEST1]", "([SLNUM]= '" & Me.
        SLNUM & "') And (
        • = '" & Me.List & "')")
          Me.CITY = DLookup("[CITY]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM &
          "') And (
          • = '" & Me.List & "')")
            Me.STATE = DLookup("[STATE]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM &
            "') And (
            • = '" & Me.List & "')")
              Me.ZIP = DLookup("[ZIP]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM & "')
              And (
              • = '" & Me.List & "')")
                Me.URL = DLookup("", "[TEST1]", "([SLNUM]= '" & Me.SL...in a cat. Bonnie http://www.dataplus-svc.com

 

[desireemm]

thank you so much bhicks11, that looks like what I need. Just some questions
since I'm not a vba programmer. where would the code go, would it go in the
Data Entry form itself and under what even AfterUpdate or something. They
would be gettting the data from the previous record they entered. The fields
used would be

[EventDate] [datetime] NULL,
[Type of Contact] [nvarchar](50) NULL,
[Purpose of Contact] [nvarchar](max) NULL,
[Referral Date] [datetime] NULL,
[Earned hours] [float] NULL,
[Catagory for hours] [nvarchar](255) NULL,
[Services Covered] [nvarchar](255) NULL,
[State Catagory] [nvarchar](255) NULL,
[State Services Covered] [nvarchar](255) NULL,

Hi Desiree

Yes you can do this. Where would they be getting the data from? The last
record or a table? In a macro you would use the setvalue function. I use
DLOOKUP to populate several fields in VBA with something like this:

If IsNull(STREET) Then
Me.LNAME = DLookup("[LNAME]", "[TEST1]", "([SLNUM] = '" & Me.SLNUM
& "') And (

• = '" & Me.List & "')")
  Me.FNAME = DLookup("[FNAME]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM &
  "') And (
  • = '" & Me.List & "')")
    Me.COMPANY = DLookup("[OFFICEORG]", "[TEST1]", "([SLNUM]= '" & Me.
    SLNUM & "') And (
    • = '" & Me.List & "')")
      Me.STREET = DLookup("[ADDRESS1]", "[TEST1]", "([SLNUM]= '" & Me.
      SLNUM & "') And (
      • = '" & Me.List & "')")
        Me.ADDRESS2 = DLookup("[ADDRESS2]", "[TEST1]", "([SLNUM]= '" & Me.
        SLNUM & "') And (
        • = '" & Me.List & "')")
          Me.CITY = DLookup("[CITY]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM &
          "') And (
          • = '" & Me.List & "')")
            Me.STATE = DLookup("[STATE]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM &
            "') And (
            • = '" & Me.List & "')")
              Me.ZIP = DLookup("[ZIP]", "[TEST1]", "([SLNUM]= '" & Me.SLNUM & "')
              And (
              • = '" & Me.List & "')")
                Me.URL = DLookup("", "[TEST1]", "([SLNUM]= '" & Me.SL...g for a macro or something??[/QUOTE] [/QUOTE]

 

[Jeff Boyce]

Are you saying that you'd have roughly the same data getting inserted into
multiple rows in a table? If so, your data design may benefit from further
normalization.

A well-normalized table structure in a relational database doesn't require
the same data/text/description/etc. being inserting again and again...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[desireemm]

No see the database use to be an ADP and MS Acess as an MDB has a short cut
key (Insert the data from the same field in the previous record CTRL+' )
Its on their Website
http://office.microsoft.com/en-us/access/HA010546551033.aspx

the thing is when you turn your Acess into and ADP that feature is no longer
supported. Because its now SQL tables not access tables.
I am trying to recreate if you will, that shortcut key because the users
like it.

 

[desireemm]

Here is the database Strucuture. I have one parent table its called the
People_tbl, and the child tables are the activities of the people. Now the
user looks up the person then enters their activities (in the child table)
Now MS has a shortcut key (Insert the data from the same field in the
previous record CTRL+' ) what i does is when the user enters in activites
from the history table which would be

[Earned Hours], [TypeOfContact], [Purpose of Contact], [Catagory for hours]
and [Services Covered]

all of these fields are in the child table (TanfActivity_tbl) not the
parent. Does that make sense??

so its the People_tbl one to many with TanfActivity_tbl

Well different People could be goign to the same acitivity for the same
amoutn of hours, thats where the Ditto key comes in. CTRL+ Apostrophe

 

[Jeff Boyce]

Can we back up for a second?

If you have a field [Services Covered], the name of this field implies that
it could hold more than one service. If this is the case, your table
structure needs further normalization. A basic rule of database design is
"one field, one fact".

If, in fact, a single record in your "child" table could have one or more
"services covered", then you need a 'grandchild' table.

For those other fields in your "child" table, I would hope that you are not
using descriptive text in fields like [TypeOfContact] and [Purpose ...] and
[Category...] ... these would seem to represent selections from a limited
list of possibilities, making them prime candidates to serve as foreign
keys, pointing back to "lookup tables" listing the choices.

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the database Strucuture. I have one parent table its called the
People_tbl, and the child tables are the activities of the people. Now the
user looks up the person then enters their activities (in the child table)
Now MS has a shortcut key (Insert the data from the same field in the
previous record CTRL+' ) what i does is when the user enters in activites
from the history table which would be

[Earned Hours], [TypeOfContact], [Purpose of Contact], [Catagory for
hours]
and [Services Covered]

all of these fields are in the child table (TanfActivity_tbl) not the
parent. Does that make sense??

so its the People_tbl one to many with TanfActivity_tbl

Well different People could be goign to the same acitivity for the same
amoutn of hours, thats where the Ditto key comes in. CTRL+ Apostrophe

No see the database use to be an ADP and MS Acess as an MDB has a short
cut
key (Insert the data from the same field in the previous record CTRL+' )
Its on their Website
http://office.microsoft.com/en-us/access/HA010546551033.aspx

the thing is when you turn your Acess into and ADP that feature is no
longer
supported. Because its now SQL tables not access tables.
I am trying to recreate if you will, that shortcut key because the users
like it.

 

[desireemm]

i understand exaclty what youi are saying and I am working on a look up table
for our comb boxes. I'm just looking for a ditto key

Can we back up for a second?

If you have a field [Services Covered], the name of this field implies that
it could hold more than one service. If this is the case, your table
structure needs further normalization. A basic rule of database design is
"one field, one fact".

If, in fact, a single record in your "child" table could have one or more
"services covered", then you need a 'grandchild' table.

For those other fields in your "child" table, I would hope that you are not
using descriptive text in fields like [TypeOfContact] and [Purpose ...] and
[Category...] ... these would seem to represent selections from a limited
list of possibilities, making them prime candidates to serve as foreign
keys, pointing back to "lookup tables" listing the choices.

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the database Strucuture. I have one parent table its called the
People_tbl, and the child tables are the activities of the people. Now the
user looks up the person then enters their activities (in the child table)
Now MS has a shortcut key (Insert the data from the same field in the
previous record CTRL+' ) what i does is when the user enters in activites
from the history table which would be

[Earned Hours], [TypeOfContact], [Purpose of Contact], [Catagory for
hours]
and [Services Covered]

all of these fields are in the child table (TanfActivity_tbl) not the
parent. Does that make sense??

so its the People_tbl one to many with TanfActivity_tbl

Well different People could be goign to the same acitivity for the same
amoutn of hours, thats where the Ditto key comes in. CTRL+ Apostrophe

No see the database use to be an ADP and MS Acess as an MDB has a short
cut
key (Insert the data from the same field in the previous record CTRL+' )
Its on their Website
http://office.microsoft.com/en-us/access/HA010546551033.aspx

the thing is when you turn your Acess into and ADP that feature is no
longer
supported. Because its now SQL tables not access tables.
I am trying to recreate if you will, that shortcut key because the users
like it.


:

Are you saying that you'd have roughly the same data getting inserted
into
multiple rows in a table? If so, your data design may benefit from
further
normalization.

A well-normalized table structure in a relational database doesn't
require
the same data/text/description/etc. being inserting again and again...


Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all I have a question can you have a macro insert data into tabls
for
you,
for faster data entry? See I was thinking of creating a form with
the
necessary fields that need to be populated. And what the user would
do is
look up the record they want to populate then hit the macro button
and
have
it insert the data into those fields. is there a way I can do that,
because
if a user has to insert the same data over and over again but for
different
records then this would make it faster for them. Can anyone tell me
how I
can do that. I'm not a programmer so i was hoping for a macro or
something??

 

[desireemm]

ok thank you for your help

Can we back up for a second?

If you have a field [Services Covered], the name of this field implies that
it could hold more than one service. If this is the case, your table
structure needs further normalization. A basic rule of database design is
"one field, one fact".

If, in fact, a single record in your "child" table could have one or more
"services covered", then you need a 'grandchild' table.

For those other fields in your "child" table, I would hope that you are not
using descriptive text in fields like [TypeOfContact] and [Purpose ...] and
[Category...] ... these would seem to represent selections from a limited
list of possibilities, making them prime candidates to serve as foreign
keys, pointing back to "lookup tables" listing the choices.

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the database Strucuture. I have one parent table its called the
People_tbl, and the child tables are the activities of the people. Now the
user looks up the person then enters their activities (in the child table)
Now MS has a shortcut key (Insert the data from the same field in the
previous record CTRL+' ) what i does is when the user enters in activites
from the history table which would be

[Earned Hours], [TypeOfContact], [Purpose of Contact], [Catagory for
hours]
and [Services Covered]

all of these fields are in the child table (TanfActivity_tbl) not the
parent. Does that make sense??

so its the People_tbl one to many with TanfActivity_tbl

Well different People could be goign to the same acitivity for the same
amoutn of hours, thats where the Ditto key comes in. CTRL+ Apostrophe

No see the database use to be an ADP and MS Acess as an MDB has a short
cut
key (Insert the data from the same field in the previous record CTRL+' )
Its on their Website
http://office.microsoft.com/en-us/access/HA010546551033.aspx

the thing is when you turn your Acess into and ADP that feature is no
longer
supported. Because its now SQL tables not access tables.
I am trying to recreate if you will, that shortcut key because the users
like it.


:

Are you saying that you'd have roughly the same data getting inserted
into
multiple rows in a table? If so, your data design may benefit from
further
normalization.

A well-normalized table structure in a relational database doesn't
require
the same data/text/description/etc. being inserting again and again...


Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all I have a question can you have a macro insert data into tabls
for
you,
for faster data entry? See I was thinking of creating a form with
the
necessary fields that need to be populated. And what the user would
do is
look up the record they want to populate then hit the macro button
and
have
it insert the data into those fields. is there a way I can do that,
because
if a user has to insert the same data over and over again but for
different
records then this would make it faster for them. Can anyone tell me
how I
can do that. I'm not a programmer so i was hoping for a macro or
something??

 

[BruceM via AccessMonster.com]

In a Purchase Order database, to use one example, it is often necessary to
place a similar order over and over. I have successfully used Allen Browne's
code to duplicate the record in form and subform:

http://allenbrowne.com/ser-57.html

Note that you can duplicate whatever fields you want, and you can insert
values (to use the PO example, today's date in the OrderDate field, for
instance) as needed.

ok thank you for your help

Can we back up for a second?

[quoted text clipped - 83 lines]