Unbound Form - Adding records to TWO tables

[mousemat]

Hi all

i have an unbound form with the following code which works as it should

Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tbl_Courses", dbOpenDynaset)
    With rst
    .AddNew
    rst!DateCreated = DateCreated
    rst!Originator = Originator
    rst!CourseTitle = CourseTitle
    rst!StartDate = StartDate
    rst!EndDate = EndDate
    rst!CourseStatus = CourseStatus.Column(0)
    rst!CourseType = CourseType.Column(0)
    rst!CourseTrainer = CourseTrainer.Column(0)
    rst!location = location
    rst!CourseFormat = CourseFormat.Column(0)
    rst!Client = Client
    rst!NumberOfDelegates = NumberOfDelegates.Column(0)
    
    Call ResetForm
    MsgBox ("This Course has been added to the Database!"), , "In House Course Added"
  
    .Update
    End With

I also need to add other text fields from the unbound form to another table but can't seem to work out how to do it.

Any help would be great.

Ian

 

[pr2-eugin]

Ian, you can create another RecordSet object and use that similar to what you have above..

However, the main underlying Question is WHY?
* Why have you not used Bound Forms?
* Why is the same/ similar information sitting in two separate tables?

 

[mousemat]

Hi paul

Thanks for your response. I hoped that I could create a new recordset, I just can't see how to create a second one.

In answer to your questions, I've not used bound forms as I want to control every aspect of the form, I know it means more work and maybe I should re-think that part of it.

In terms of the two tables, one table is for courses that have been booked and they relate to resources from another table.

Tables are courses book and resourcesforcourse. The idea behind it is as a resource scheduling database. We have three sales reps selling courses, we have a limited number of laptops etc and trainers, so the idea is to stop sales reps double booking course etc

 

[pr2-eugin]

Creating and using another RecordSet..

Dim rst As DAO.Recordset, [COLOR=Blue][B]anotherRS As DAO.Recordset[/B][/COLOR]
Set rst = CurrentDb.OpenRecordset("tbl_Courses", dbOpenDynaset)
[COLOR=Blue][B]Set anotherRS = CurrentDb.OpenRecordset("tbl_ResourcesForCourses", dbOpenDynaset)[/B][/COLOR]
With rst
    .AddNew
    rst!DateCreated = DateCreated
    rst!Originator = Originator
    rst!CourseTitle = CourseTitle
    rst!StartDate = StartDate
    rst!EndDate = EndDate
    rst!CourseStatus = CourseStatus.Column(0)
    rst!CourseType = CourseType.Column(0)
    rst!CourseTrainer = CourseTrainer.Column(0)
    rst!location = location
    rst!CourseFormat = CourseFormat.Column(0)
    rst!Client = Client
    rst!NumberOfDelegates = NumberOfDelegates.Column(0)
    Call ResetForm
    MsgBox ("This Course has been added to the Database!"), , "In House Course Added"
    .Update
End With

[COLOR=Blue][B]With anotherRS[/B][/COLOR]
[COLOR=Blue][B]    .AddNew[/B][/COLOR]
    [COLOR=Green]' add the fields[/COLOR]
[COLOR=Blue][B]End With
Set rst = Nothing
Set anotherRS = Nothing[/B][/COLOR]

I am not sure how your Relationships are designed, but sure can be done with Bound Forms.. This will make your life very (trust me a lot much) easier.. Everyone here will be happy to help you out..

To avoid Double booking, you can use DCount. If you create a properly designed DB, they will link one Sales agent to their sales, thus they will never double book the same course.. Think over it.. Solution might be easier than you think.. Good Luck !!

 

[mousemat]

Paul

Many thanks for your.

I will take on board your comments about using Bound Forms for this particular project though and I think you are right, I am complicating things probably way too much!!!

 

[pr2-eugin]

Glad to have helped..

Think about it, if you are stuck up with something, explain what you want.. Sure we would be happy to help..

 

[mousemat]

Paul

Here is my scenario.

I am a software trainer for a company that offer the following:

In House Courses for specific clients at the clients offices
Public Course at a hotel / conference centre for anyone to come along to.

I am one of two trainers.
We have twelve laptops.
We have one projector.
We offer several types of course ranging from one to two days, but the trainers have to travel to courses, depending upon where they are the day before.

I want to be able to get the sales reps to uses the database to input the dates, numbers of delegates, whether its a public or in house and how many laptops are required, upon entering this information, pressing a button an checking that all of the required resources are available (Trainer, laptop, projector)

I have several tables:

Trainers - Trainer Name etc
Resources - Laptops, projector etc
Course Status - Confirmed, provisional etc
Course Type - Public or In house

Am i missing something or over complicating things.

Just looking at the list of tables, should I combine Resources and trainers as essentially, I am a resource too?