Access VB, new records created from variables and loops etc

[argowen]

i'm working on a course booking system using access which is heavily coded using vb however I have stumbled onto something that a few eyes maybe able to put a bit of focus on.

creating new course dates that follow a repetition:
I have a form with a couple of combo boxes a list box and a calendar control, the user is asked what day to repeat the course on, and using a list box they can add what times to start the course (so we can have courses starting at 0900, 1100, 1300 and 1500: every monday (mainly)), then the user is asked to select how long (in weeks) to run the repetition for. finally the calendar is used to determine when to run the repetition from so it could run for mondays at 0900 for 6 weeks starting from march 2009.

now my query is this I can set up and have all my data/information to be able to send into a form however I was planning on coding that action is there a way to assign variables in the addnewrecord state so that I can make a loop statement or would i have to actually assign these variables to fields in a form and then record them into the table?

any help would be greatly appreciated.

 

[namliam]

You will have to open a "recordset" to add the records into the table.

 

[argowen]

You will have to open a "recordset" to add the records into the table.

Set iCourses = CurrentDb.OpenRecordset("oCourses")

With iCourses
.AddNew
!SessionDate = SessionNumber_WorkingDate
!SessionTime = lstAt.ItemData(iTimeLoop - 1)
.Update
.Close
End With
​

Set iCourses = Nothing
​

This is what I've currently got but doesn't seem to work within access, oCourses = table of course dates {CourseId: Autonumber, Course date, course time, IsInduction: boolean}

SessionNumber_WorkingDate is a date that is incremented in a loop when creating repetition of identical courses on different dates, lstAt is a list box so times can be added and iTimeLoop is a FOR LOOP var for making sure that all times are added basically the script this belongs to works fine apart from the bit above.

i.e. User clicks command button. (if we are creating 3 sessions for 1/12/08 starting at 0900, 1100 and 1300.

if current date is a monday then for itimeloop =1 to lstAt.listcount
add the sessions into the oCourses table.

the only var that need assigning to table recordset are CourseDate and CourseTime as the IsInduction variable is not really going to be used when adding repetitions of courses.

 

[namliam]

It is not working but WHAT is not working?

You are describing things (i.e. iTimeLoop) which are irrelevant to this peace of code which is only clouding the issue...

Random ideas:
Is iCourses a DAO recordset?
Try "Select * from iCourses" that helps sometimes to work with a query instead of the direct table.
.Close closes the recordset, i.e. you have to re-open it. Normaly one opens a recordset right at the start of code... then only closes it just prior to "End Sub"

 

[argowen]

I've sorted it I was opening the inital connection in the wrong method the working code is:

Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "oCourses", conn, adOpenDynamic, adLockOptimistic, adCmdTable
With rs
.AddNew
!SessionDate = SessionNumber_WorkingDate
!SessionTime = lstAt.ItemData(iTimeLoop - 1)
.Update
.Close
End With

thanks for the inital info though you pointed me in the right directions.
cheers

 

[namliam]

Random ideas:
Is iCourses a DAO recordset?

So after all it was a ADO recordset instead of a DAO one...

DAO really is better IMHO as access itself is DAO based.

 

[argowen]

Well it could have technically been any but for this particular portion I just needed a way to code variable values into a record in a table; the previous methods I'd tried kept failing. TBH working with database's AND vba code isn't my forté, I'm more of a games programmer, i mainly use coded database files sequential etc.. rather than using progs like access.

but cheers anyway

 

[Balr14]

So after all it was a ADO recordset instead of a DAO one...

DAO really is better IMHO as access itself is DAO based.

Please explain. How can an Access table be either? Isn't ADO or DAO just the means and properties used to access it?

 

[namliam]

A form recordset for example is DAO...
Set rs = Me.Recordsetclone

Can only be done if the RS is a DAO recordset, Jet itself is also DAO...