INSERT INTO with MS ACCESS and VB6.0

[Jerielsky]

Hi everyone.. ,

I'm trying to create a simple Patient Information System and I have no idea on how to Add a new patient record in the database.
I know how to add a record using RS.AddNew and RS.Update like:

RS.AddNew
RS.Fields("PatientNo").value = txtPatientNo.text
RS.Fields("FirstName").value = txtFName.text
RS.Fields("LastName").value = txtLName.text
'And so on..
RS.Update

But what I actually want to do is Insert this new record in a specific page number and book number. Because here, I have tables
PAGE
PK - PageNo

BOOK
PK - BookNo

PATIENT
PK - PatientNo
- FirstName
- LastName
- MiddleName
- Address
- Age

'Junction Table for the Ternary Relationship
BOOKPAGEPATIENT
PK - BookNo
PK - PageNo
PK - PatientNo

The program goes like this, I open a Book Number, after opening a book number, I can open a Page from this Book that is currently opened. Once that a Page number is opened, I now have some operations like Add, Delete, Save, Edit and Cancel. But I'm actually done with some of the few buttons here.

My problem only is how to add a new Patient record in the currently opened Book and Page No.

Here is my code on the database connection which connects to ms access database:

Global DBS As New ADODB.Connection
Global patientListRS As New ADODB.Recordset

'Database Connection
DBS.CursorLocation = adUseClient
DBS.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\CLINIC 97.mdb;MODE=ReadWrite"

set patientlistrs.activeconnection = DBS
patientlistrs.locktype = adlockoptimistic
patientlistrs.source = "PATIENT"
patientlistrs.open , DBS, , , adcmdTable

Once that I click on the add button,
The textboxes are cleared for entry of new records and which will enable the textboxes and the Save and Cancel buttons and disable all the other buttons.

I don't know what code to use when on the save button. Please help me..

[si_the_geek]

The save part you showed (using .Update) is valid for the first half.

The part you need to do is add a record to BOOKPAGEPATIENT, using the PatientNo you used already, and the BookNo and PageNo which you also seem to know the values of. Using an Insert Into statement would be a good way to add it.

For information on how to do that (and comparison of the various methods), see the article How can I add a record to a database? from our Database Development FAQs/Tutorials (at the top of this forum)


For the part you currently have, it seems that it would be more efficient to not use a recordset there either - because you are currently loading all of the existing data (rather than just relevant data), when you don't even need any of it for adding more data.