Access Modules

[Kissi Asiedu]

I am trying to find a way to append data from a user into a table using Code
module. Here is an example:
I have a table "SHORTS" and fields "DATE" and "SHORT". I wrote the code
below but access won't recognize the fields. Can some one Help please.
Dim strShorts as Sting
Dim dtShort as Date
dtDhort = Date
strShorts = inputbox ("Enter Shorts", "Today's Shorts")
[SHORTS]![DATE] = dtShort
[SHORTS]![SHORT] = strShorts

[Mark]

Re: Access Modules

I'm no programmer but I know that DATE is a reserved word which you should
use as a field name. Maybe that's the problem???

Mark

"Kissi Asiedu" <kissi5559@aol. com> wrote in message
news:3ssEe.5685 7$kb4.21359@fe7 8.usenetserver. com...
I am trying to find a way to append data from a user into a table using Code
module. Here is an example:
I have a table "SHORTS" and fields "DATE" and "SHORT". I wrote the code
below but access won't recognize the fields. Can some one Help please.
Dim strShorts as Sting
Dim dtShort as Date
dtDhort = Date
strShorts = inputbox ("Enter Shorts", "Today's Shorts")
[SHORTS]![DATE] = dtShort
[SHORTS]![SHORT] = strShorts

[fredg]

Re: Access Modules

On Sat, 23 Jul 2005 10:25:50 -0400, Kissi Asiedu wrote:
[color=blue]
> I am trying to find a way to append data from a user into a table using Code
> module. Here is an example:
> I have a table "SHORTS" and fields "DATE" and "SHORT". I wrote the code
> below but access won't recognize the fields. Can some one Help please.
> Dim strShorts as Sting
> Dim dtShort as Date
> dtDhort = Date
> strShorts = inputbox ("Enter Shorts", "Today's Shorts")
> [SHORTS]![DATE] = dtShort
> [SHORTS]![SHORT] = strShorts[/color]

Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
Change the field named "Date" to something else, perhaps "SaleDate".

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

How you update the table depends upon other factors.
Where did you place this code? In a form's Code Module, or in an
Application Module?

In a Form module, if [YourDateField] and [Short] are included in the
form's record source, you can use:

(Note that strShorts in your example above is Dimmed as Sting.
It should be String. However, there is no need to Dim any variables.)

[YourDateField] = Date ' Enter the current date into [YourDateField]
[SHORT] = inputbox ("Enter Shorts", "Today's Shorts")

In an Application Module run an Update query (look up Update Query in
Access help) or open the recordset and make the change directly.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[John Welch]

Re: Access Modules


"Kissi Asiedu" <kissi5559@aol. com> wrote in message
news:3ssEe.5685 7$kb4.21359@fe7 8.usenetserver. com...[color=blue]
>I am trying to find a way to append data from a user into a table using
>Code module. Here is an example:
> I have a table "SHORTS" and fields "DATE" and "SHORT". I wrote the code
> below but access won't recognize the fields. Can some one Help please.
> Dim strShorts as Sting
> Dim dtShort as Date
> dtDhort = Date
> strShorts = inputbox ("Enter Shorts", "Today's Shorts")
> [SHORTS]![DATE] = dtShort
> [SHORTS]![SHORT] = strShorts[/color]

You can't just say "set this field in the table to strShort", because it
won't know which record you are talking about: a new one?, the first one? ,
etc. So you have to run an append query or append to a recordset. The first
is easiest and best in this case:

Dim strShorts as string
dim strSQL as string
strShorts = inputbox ("Enter Shorts", "Today's Shorts")
strSQL ="INSERT INTO Shorts ( datefield, short) SELECT Date() , '" &
strShorts & "' ;"
DoCmd.RunSQL (strSQL)

Notice I've changed the field name "date" to "datefield" . You can use what
you want, but you shouldn't use special words like "date" as field names,
because they have other meanings to Access and could cause problems.

Do you have a good intro book on VB for Access? If not, it would be a good
investment.

[Salad]

Re: Access Modules

Kissi Asiedu wrote:
[color=blue]
> I am trying to find a way to append data from a user into a table using Code
> module. Here is an example:
> I have a table "SHORTS" and fields "DATE" and "SHORT". I wrote the code
> below but access won't recognize the fields. Can some one Help please.
> Dim strShorts as Sting
> Dim dtShort as Date
> dtDhort = Date
> strShorts = inputbox ("Enter Shorts", "Today's Shorts")
> [SHORTS]![DATE] = dtShort
> [SHORTS]![SHORT] = strShorts[/color]

Where is [SHORTS]![DATE] and [SHORTS]![SHORT] comming from? As noted by
others, if a form, assign it to the name in the form. If a recordset,
you need to edit/assign/update the values
Dim rst As Recordset
set rst = CUrrentdb.openr ecordset("Table ",dbopendynaset )
rst.findfirst "ShortID = " & 123
rst.edit
rst!Date = dtShort
rst!Short = strShorts
rst.Update
rst.close
set rst = Nothing
or you can create an update query.

Simply specifying the table and field will not do anything.

[Kissi Asiedu]

Re: Access Modules

Thank you guys, you've been of great help.
"Salad" <oil@vinegar.co m> wrote in message
news:2GuEe.2732 $0C.1200@newsre ad3.news.pas.ea rthlink.net...[color=blue]
> Kissi Asiedu wrote:
>[color=green]
>> I am trying to find a way to append data from a user into a table using
>> Code module. Here is an example:
>> I have a table "SHORTS" and fields "DATE" and "SHORT". I wrote the code
>> below but access won't recognize the fields. Can some one Help please.
>> Dim strShorts as Sting
>> Dim dtShort as Date
>> dtDhort = Date
>> strShorts = inputbox ("Enter Shorts", "Today's Shorts")
>> [SHORTS]![DATE] = dtShort
>> [SHORTS]![SHORT] = strShorts[/color]
>
> Where is [SHORTS]![DATE] and [SHORTS]![SHORT] comming from? As noted by
> others, if a form, assign it to the name in the form. If a recordset, you
> need to edit/assign/update the values
> Dim rst As Recordset
> set rst = CUrrentdb.openr ecordset("Table ",dbopendynaset )
> rst.findfirst "ShortID = " & 123
> rst.edit
> rst!Date = dtShort
> rst!Short = strShorts
> rst.Update
> rst.close
> set rst = Nothing
> or you can create an update query.
>
> Simply specifying the table and field will not do anything.
>[/color]