Saving multiple records by date (1 Viewer)

hbtousa · Jan 25, 2007

Hi. I want to be able to save multiple records sequentially by date for the same person. I have a ssn, a date and number of days field. I imagine I need a loop to get it done. I really don't know how to write the code for this I tried couple things and I ended up with nothing. Thanks for your help.

FoFa · Jan 25, 2007

basically
Insert the first record
For loop the number of days you have minus one.
Add 1 day to your date using DATEADD
Insert next record
Next Loop

hbtousa · Jan 25, 2007

Thanks for your answer. I tried this but is only saving one single record instead of the number of records determined by the numeric variable. I am not using the dateadd part yet, since my single loop is not giving me result I am looking for. What I am missing?.Big Thanks.This is the code:
Private Sub Command40_Click()
DoCmd.Save acDefault
Dim i As Variant
Set dbs = CurrentDb
For i = 1 To Me.Form.Text41 - 1
DoCmd.GoToRecord , , acNewRec
DoCmd.Save acDefault
Next i

On Error GoTo Err_Command40_Click

Exit_Command40_Click:
Exit Sub

rborob · Jan 25, 2007

have you debugged it to see what its doing wrong? where is it breaking? is it looping at all?

hbtousa · Jan 25, 2007

I don't know how to "debugg". I have and add record button and a save record button. Both buttons are working since I am able to add the record and save it, but is ignoring my loop and my variable that allows me to contol how many records I want to duplicate
Thanks

rborob · Jan 25, 2007

debug is where you insert breakpoints in your code to "step" through it and find what is going on behind the scenes. That way you can see how the code is executed and if it doing what you want it to do

hbtousa · Jan 25, 2007

I didn't acomplish to much with the debugging. Dont know how to use it properly .Still the same result. What could be the cause of not doing the multiple recording with the loop. Thanks

Fear Naught · Jan 25, 2007

Your code doesn't really appear to do anything. You go to a new record but then don't put any data into it. You then effectively save a blank record.

Am I missing something here !! :-(

Fear Naught · Jan 25, 2007

Ah - just re-read the code. i assume that the line:

Code:

DoCmd.Save acDefault

saves the record with default values

Dohhhh

FoFa · Jan 25, 2007

See, I would something along these lines (code syntax maybe not 100%):

Code:

   Dim cnn As New ADODB.Connection
   Dim iAffected As Integer
   Dim sSQL1 as string, sSQL2 as string, LP as integer
   Dim NewDate as date

   Set cnn = CurrentProject.Connection

   sSQL1 = "INSERT INTO YOURTABLE (Col1,Col2,Col3) SELECT "

   ' Run the Loop
   FOR LP = 0 to Me.Form.Text41 - 1
     NewDate = DATEADD("d",LP,Me.DateField)
     sSQL2 = "'" & Me.StringValue & "',#" & Format(NewDate,"MM/DD/YYYY") & "#," & Me.NumericValue
     ' Execute the query
     cnn.Execute sSQL1 & sSQL2, iAffected, adExecuteNoRecords
   NEXT LP

   ' Close the connection
   cnn.Close

hbtousa · Jan 25, 2007

I got it and I want to share it. Instead of using the add button record I used the duplicate record button. Works awsome. Thanks four your answers. I will try the adddate part. I think I should not have a problem with it. This is the code . Thanks
Private Sub Command43_Click()
On Error GoTo Err_Command43_Click
Dim i As Variant
For i = 1 To Me.Form.Text41 - 1

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Next i

Exit_Command43_Click:
Exit Sub

Err_Command43_Click:
MsgBox Err.Description
Resume Exit_Command43_Click

End Sub

Saving multiple records by date (1 Viewer)

hbtousa

Registered User.

FoFa

Registered User.

hbtousa

Registered User.

rborob

Registered User.

hbtousa

Registered User.

rborob

Registered User.

hbtousa

Registered User.

Fear Naught

Kevin

Fear Naught

Kevin

FoFa

Registered User.

hbtousa

Registered User.

Similar threads

Users who are viewing this thread