8 Replies - 26752 Views - Last Post: 16 August 2011 - 11:36 AM Rate Topic: -----

#1 zenfan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-August 11

How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 03:28 AM

Hello guys,

i have been trying to get a code working for the last few days but in vain. i have a form linked to an access database. The problem is that when i'am saving from the form , i want the data to be saved in two different tables.
The first table name : tblMedecine
the second table name : tblBatch

I want only the 'expiry date' and the 'batch number ' to be saved to tblBatch database.
and the rest should be saved in the tblMedecine database.

here is the code that my teacher gave me :
  
Call connectdatabase


rs.Open "Select * from tblMedecine where MedecineID='" & cbomedid.Text & "'", con, adOpenKeyset, adLockOptimistic
   If rs.RecordCount > 0 Then 'medicine details already exist
        With rs
.Fields("Name") = cbomedname.Text
.Fields("MedecineID") = cbomedid.Text
.Fields("Price") = txtprice.Text
.Fields("Type") = txttype.Text
.Fields("DrugInteractions") = txtdrug.Text
.Fields("SideEffects") = txtside.Text
.Fields("OverdosageSymptoms") = txtover.Text
.Fields("CountryOforigin") = txtorigin.Text
.Fields("SupplierID") = supid



.Update
.Close
End With
con.Close
Else 'add new medicine details
rs.AddNew

With rs
.Fields("Name") = cbomedname.Text
.Fields("MedecineID") = cbomedid.Text
.Fields("Price") = txtprice.Text
.Fields("Type") = txttype.Text
.Fields("DrugInteractions") = txtdrug.Text
.Fields("SideEffects") = txtside.Text
.Fields("OverdosageSymptoms") = txtover.Text
.Fields("CountryOforigin") = txtorigin.Text
.Fields("SupplierID") = supid


.Update
.Close
End With
con.Close

'save batch details in tblbatch
con.Open

With rs
.Fields("BatchNum") = txtbatch.Text
.Fields("MedicineID") = cbomedid.Text
.Fields("ExpiryDate") = txtexpiry.Text

.Update
.Close
End With
 'Displaying message that the records have been successfully added
MsgBox "Medicine records added"

endsub






see the form in the attachment

i tried the code but it doesn't work :S ..could someone correct the code for me if there are any mistakes.

thanks

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: How to save data in two different tables in access from VB6 form.

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 14033
  • View blog
  • Posts: 56,137
  • Joined: 12-June 08

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 06:56 AM

Define "doesn't work". Is there an exception - if so what is the exception text and what line is it occurring on? Is it producing actions that are not intended? If so what are the actions and what should have they been?

This site isn't some "debug by proxy". Just dumping your code out with a generic "it doesn't work" and then asking for someone to fix it is a horrible way to learn or develop your skills.

Help us to help you!
Was This Post Helpful? 0
  • +
  • -

#3 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 330
  • View blog
  • Posts: 1,997
  • Joined: 26-March 09

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 07:01 AM

Where do you actually read the record from the Batch table to update?
Was This Post Helpful? 0
  • +
  • -

#4 zenfan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-August 11

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 07:51 AM

My apologies sir, i haven't given a proper detailed explanation.
The form above is used to record details about a particular medecine.
Clicking on save button would save the details to a table(tblmedecine) found in my database.
Now,let's say a new batch of medecine has arrived. The details such as Name, MedecineID, BuyingPrice, Type, Drug interactions, Side effects , OverdosageSymptoms , Country of origin and country would be the same , so the data would just be updated in tblMedecine. But the batch number and expiry date won't be the same. What i want is , when i am saving data about a new batch of medecine, the other fields such as MedecineID, Buying price would just be updated in the tblMedecine whereas data from the textbox "BatchNumber" and "expirydate" would be saved in another table called tblbatch. Using the code above, when clicking on the save button, the details about the new batch of medecine gets updated but the data from "expirydate" and "BatchNumer" in the above form doesnt get filled in the tblBatch as i woul have liked it to be. I hope i have produced a clearer documentation about my problem.
How can i fix the above code..?
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 14033
  • View blog
  • Posts: 56,137
  • Joined: 12-June 08

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 07:55 AM

Where do you tell the recordset what table to update? As far as I can tell you are asking it to update fields that do not exist in the first table.
Was This Post Helpful? 0
  • +
  • -

#6 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 330
  • View blog
  • Posts: 1,997
  • Joined: 26-March 09

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 08:12 AM

View Postzenfan, on 16 August 2011 - 08:51 AM, said:

My apologies sir, i haven't given a proper detailed explanation.
The form above is used to record details about a particular medecine.
Clicking on save button would save the details to a table(tblmedecine) found in my database.
Now,let's say a new batch of medecine has arrived. The details such as Name, MedecineID, BuyingPrice, Type, Drug interactions, Side effects , OverdosageSymptoms , Country of origin and country would be the same , so the data would just be updated in tblMedecine. But the batch number and expiry date won't be the same. What i want is , when i am saving data about a new batch of medecine, the other fields such as MedecineID, Buying price would just be updated in the tblMedecine whereas data from the textbox "BatchNumber" and "expirydate" would be saved in another table called tblbatch. Using the code above, when clicking on the save button, the details about the new batch of medecine gets updated but the data from "expirydate" and "BatchNumer" in the above form doesnt get filled in the tblBatch as i woul have liked it to be. I hope i have produced a clearer documentation about my problem.
How can i fix the above code..?


As I put in my post above this and Modi has mentioned below this, you need to have a record to update first from the Batch table.
Was This Post Helpful? 0
  • +
  • -

#7 zenfan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-August 11

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 11:05 AM

View Postmaj3091, on 16 August 2011 - 08:12 AM, said:

View Postzenfan, on 16 August 2011 - 08:51 AM, said:

My apologies sir, i haven't given a proper detailed explanation.
The form above is used to record details about a particular medecine.
Clicking on save button would save the details to a table(tblmedecine) found in my database.
Now,let's say a new batch of medecine has arrived. The details such as Name, MedecineID, BuyingPrice, Type, Drug interactions, Side effects , OverdosageSymptoms , Country of origin and country would be the same , so the data would just be updated in tblMedecine. But the batch number and expiry date won't be the same. What i want is , when i am saving data about a new batch of medecine, the other fields such as MedecineID, Buying price would just be updated in the tblMedecine whereas data from the textbox "BatchNumber" and "expirydate" would be saved in another table called tblbatch. Using the code above, when clicking on the save button, the details about the new batch of medecine gets updated but the data from "expirydate" and "BatchNumer" in the above form doesnt get filled in the tblBatch as i woul have liked it to be. I hope i have produced a clearer documentation about my problem.
How can i fix the above code..?


As I put in my post above this and Modi has mentioned below this, you need to have a record to update first from the Batch table.









With rs

.Fields("BatchNum") = txtbatch.Text

.Fields("MedicineID") = cbomedid.Text

.Fields("ExpiryDate") = txtexpiry.Text




i use this part to save data from the textboxes batchNumber and expiry date and the combo box cbomedid into the table Batch (tblbatch). i am sure there must be some mistake in the above code. Could you point out my error ?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 14033
  • View blog
  • Posts: 56,137
  • Joined: 12-June 08

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 11:16 AM

Like it has been mentioned before - you have no reference to a batch from table "tblbatch" before you try to insert into it.
Was This Post Helpful? 0
  • +
  • -

#9 zenfan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-August 11

Re: How to save data in two different tables in access from VB6 form.

Posted 16 August 2011 - 11:36 AM

Excuse me sir, i am a beginner and i don't quite get your point. In tblBatch, there is a field "BatchNum" in which data should be filled into from the textbox "Batch Number" of the medecine form. Is that what you are asking..? i don't think i am understanding the meaning of the word "reference" correctly here . Forgive for my lack of knowledge in this field.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1