My pleasure.... George
-----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of kingfisher2xx4 Sent: Tuesday, October 03, 2006 7:03 PM To: [email protected] Subject: [ms_access] Re: Using VB in Access 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 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/
