Data Entry through a macro

  • Thread starter Thread starter desireemm
  • Start date Start date
D

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

bhicks11 via AccessMonster.com said:
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]
Click to expand...
 
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
 
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.
 
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
 
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

desireemm said:
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


desireemm said:
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.
Click to expand...
Click to expand...
 
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

Jeff Boyce said:
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

desireemm said:
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


desireemm said:
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??
Click to expand...
Click to expand...
Click to expand...
 
ok thank you for your help

Jeff Boyce said:
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

desireemm said:
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


desireemm said:
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??
Click to expand...
Click to expand...
Click to expand...
 
Back
Top