That is great, thanks for the jumpstart George all your help.
I had to change a couple things, here is the updated code. Your
suggestion gave me exactly what I needed. I added a command to delete
the records before entering the For...Next loop, so that it starts with
a clean table.
Thanks again.
************************************************************************\
*****************
Dim i As Integer
Dim cn As ADODB.Command
Dim dtmDate As Date
Dim strINSERT As String
Set cn = New Command
cn.ActiveConnection = CurrentProject.Connection
cn.CommandText = "DELETE TimePeriodDates.DateWorked FROM
TimePeriodDates"
cn.Execute
For i = 0 To 13
dtmDate = Me.DW_PeriodEnd_combo - i
cn.CommandText = "INSERT INTO TimePeriodDates (DateWorked)
VALUES (#" & dtmDate & "#)"
cn.Execute
Next i
Set cn = Nothing
************************************************************************\
*****************
---In [email protected], "George Oro" <[EMAIL PROTECTED]> wrote:
>
> try this:
>
> dim cn as adodb.connection
> dim dtmDate as date
> dim strINSERT as string
>
> for i = 0 to 13
> dtmDate=me.DW_PeriodEnd_combo - i
> strINSERT="INSERT INTO TimePeriodDates (DateWorked) VALUES (#"&
dtmDate
> &"#)"
> cn.execute(strINSERT)
> next i
>
> cn.closet
> set cn=nothing
>
>
>
>
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED]
> Behalf Of kingfisher2xx4
> Sent: Tuesday, October 03, 2006 5:24 PM
> To: [email protected]
> Subject: [ms_access] Re: Using VB in Access
>
>
>
> I found a solution, using a form and query though I am sure there is a
> more eloquent programatic implementation.
>
> The user selects a time period end date from a combo box on the form,
> clicks a button and a sub loops through a For..Next generates dates
for
> 14 days leading up to and including the PeriodEnd Date assigning the
> date value to a text box on the form [CurrentDate]. As each date is
> created an append query is executed that references the [CurrentDate]
> form control.
>
> For..Next Loop
>
> For i = 0 To 13
> [Forms]![DW_Select_Form]![CurrentDate] =
> ([Forms]![DW_Select_Form]![DW_PeriodEnd_combo] - i)
> DoCmd.OpenQuery "TimePeriod_Add_DW"
> Next
>
>
> "TimePeriod_Add_DW" SQL code:
>
> INSERT INTO TimePeriodDates ( DateWorked ) SELECT
> Forms!DW_Select_Form!CurrentDate AS CurrentDate;
>
>
>
> --- In [email protected], "George Oro" george@ wrote:
> >
> > Sorry...
> > BE = Back End or ur database and
> > FE = Front End or ur application
> >
> > so basically u want to add a new record and assign a value to ur
date
> field
> > using VBA?
> >
> >
> >
> > -----Original Message-----
> > From: [email protected] [mailto:[EMAIL PROTECTED]
> > Behalf Of kingfisher2xx4
> > Sent: Tuesday, October 03, 2006 4:22 PM
> > To: [email protected]
> > Subject: [ms_access] Re: Using VB in Access
> >
> >
> > I am using Access, and I guess I need to go to acromyn school
> > because I have no idea what you are using BE and FE to refer to.
> >
> > --- In [email protected], "George Oro" george@ wrote:
> > >
> > > r u talking about using VBA or Access just BE and VB is ur FE?
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: [email protected] [mailto:[EMAIL PROTECTED]
> > On
> > > Behalf Of kingfisher2xx4
> > > Sent: Tuesday, October 03, 2006 3:47 PM
> > > To: [email protected]
> > > Subject: [ms_access] Using VB in Access
> > >
> > >
> > > I have used access (v. 2003) for a number of years an have used
> > > structured progamming on a regular basis. I am somewhat familiar
> > with
> > > programming objects.
> > >
> > > I am venturing into using VB in Access. I am having some trouble
> > > getting started. I have a table with a single date field that I
> > want
> > > to add records to and assign a value to that record.
> > >
> > > A form with a button initates the process and I want to use a for
> > next
> > > loop to append a blank record and assign a date value to the new
> > > record in the table.
> > >
> > > How do I execute the query from the VB procedure, the DoCmd or do
> > I
> > > reference it as an AccessObject?
> > >
> > > Thanks for your help!
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
> Yahoo! Groups Links
>
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/ms_access/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
