Adding Data to a Table

[Guest]

I would like to know how I can automatically add data to a table. Assume a
user can enter a beginning date and an ending date and one other field. I
need to take the dates and the other field and create X records. The X part
is a simple loop, but how do I open a table and insert records into it. The
records I would insert woud consist of Date and the other field name.

Thanks

 

[Guest]

hi
this is code i use to add a new record to one of my tables
it is attached to a button. after user fills in the form,
they click the button and zaps it into the table.
change my fields to your fields and stick it in a loop.
good luck
Private Sub cmdAccept_Click()
On Error Resume Next
Dim Dbs_Disc As Database
Dim rsDisc As Recordset
Set Dbs_Disc = CodeDb()
'table name
Set rsDisc = Dbs_Disc.OpenRecordset("Disc", dbOpenDynaset)
With rsDisc
.AddNew
'table field form field
!dis_ControlNbr = txtControlNbr
!dis_ItemID = txtItemID
!dis_Description = txtDescription
!dis_VendorName = txtVendorName
!dis_POonPackSlip = txtPOonPackSlip
!dis_PackSlipNbr = txtPackSlipNbr
!dis_PackSlipQty = txtPackSlipQty
!dis_POQty = txtPOQty
!dis_OKICountQty = txtOKICountQty
!dis_OverUnderPO = txtOverUnderPO
!dis_OverUnderPS = txtOverUnderPS
!dis_Other = txtOther
!dis_Recdby = txtRecdBy
!dis_DateRecd = txtDateRecd
!dis_Status = txtStatus
!dis_InstructionComments = txtInstructionComments
!dis_Buyer = txtBuyer
!dis_DateDispositioned = txtDateDispositioned
.Update
End With
rsDisc.Close
Dbs_Disc.Close
MsgBox (" Record was added.")
me.requery
Call Clear_Form_Click
end sub

 

[John Vinson]

I would like to know how I can automatically add data to a table. Assume a
user can enter a beginning date and an ending date and one other field. I
need to take the dates and the other field and create X records. The X part
is a simple loop, but how do I open a table and insert records into it. The
records I would insert woud consist of Date and the other field name.

Thanks

It may not be necessary to do this - you may well be able to generate
these records simply using a Query.

If you do want to do so anyway, an Append query would be the simplest
approach. What you might want to do is create a small auxiliary table
AllDates, with all the dates you might want to use for the next ten
years (3653 rows? a tiny table). You can generate this table quickly
in Excel using the spreadsheet fill-down capability, and just import
the spreadsheet into a new table.

You can then create a query by putting criteria

BETWEEN [Enter start date:] AND [Enter end date:]

on this field. You may be able to use this query directly, or change
it to an Append query to permanently store the dates.

John W. Vinson[MVP]

 

[Guest]

Thanks! Now it does add records, but does not fillin the information.

Private Sub CreateSchedule_Click()

Dim Evt, Crd, Dy, Mth, Tmcde, Envt As String
Dim Btd, Edt, I As Date
Dim Lgth, BT As Integer
Dim Ddbase As Database
Dim Recset As Recordset
Set Ddbase = CodeDb()


BT = Me!Start
Crd = Me!SUper
'The following creates the unique Event ID based on Dates and Jobs
For I = Me!BDate To Me!EDate
Date = I
Mth = Trim(Str(Month(I)))
Do While Len(Mth) < 2
Mth = "0" + Mth
Loop
Dy = Trim(Str(Day(I)))
Do While Len(Dy) < 2
Dy = "0" + Dy
Loop
Envt = Jobs + "-" + Dy + Mth + "E" + Stime
'The Following will open a databse and create a recordset & Add Records

Set Recset = Ddbase.OpenRecordset("zztempeventslist", dbOpenDynaset)
With Recset
.AddNew
EventID = Envt
EvtDate = Date
SCODE = Me!SUper
.Update
End With
Recset.Close
Next I

End Sub