One form accessing three tables

[pingpang]

Hi! I need help...

I have 3 tables: Customer, Job Order(main), Job Order Grid.

Customer table fields: c_id(primary key) autonumber, c_name, c_address.

Job Order(main) fields: jo_no(primary key)autonumber,jo_ordered-date, c_id(foreign key)

Job Order Grid fields: jod_id, jod_qty, jod_unit, jod_desc, jod_uprice, jod_amt, jo_no(foreign key).

RELATIONSHIPS: One Customer - Many Job Order(main)
One Job Order(main) - Many Job Order Grid

Using ADODC
My form: jo_no-textbox
c_name-combo box
c_address-textbox
jo_ordered-datepicker
Im using datagrid control for my Job Order Grid.

My problem is that I don't know how to accomplish this task. If you click a save button on the form, how would it save the data in the 3 tables?

If you don't get what I mean I would like to send you my vb project to see it for yourself.

I hope you could help me...

 

[johnwm]

Without writing the whole code you could start from here:

1. Check if customer exists - if not save customer data to customer table

2. Check if job_no exists - if not save job_no data to Job table

3. Loop through Job_grid data saving each item to job_grid table

You may find it safer to use a separate form to generate new customer data, then use combo (or similar) filled from customer table to generate new jobs.

Similarly to add job lines, use job_no combo for the selected customer.

This will help guard against duplicate customers and jobs.

Hope this is enough to get you started
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[pingpang]

Thanks for the response...

I have actually made the forms already. I have a separate form for the customer.
Another form is for the Job Order where I provide a combo box for a list of customers. Also, in that Job Order form are a textbox for Job Order No.(jo_no) and a datepicker for jo_ordered. Within this form, I put a datagrid to view(doesn't update,delete,add) all the data in the Job Order Grid form for that particular Job Order No.(jo_no). I also placed a command button where you can open the Job Order Gird form - to add data in the Job Order Grid for a particular jo_no.

In short I have three forms:
1. Customer form
2. Job Order form (has the datagrid which displays the data in the Job Order Grid form for a particular jo_no.)
3. Job Order Grid from

When I click the command button on my Job Order form that opens my Job Order Grid form, the only data present when the form(Job Order Grid) load is the jo_no which is the jo_no from the Job Order form. By the way, my Job Order Grid form has two command buttons, one for saving and another for canceling. When Im done inputing datas from ny Job Order Gird form, I will clicked the save button. An error occurs "(Run time error '-2147467259 (80004005)': You cannot add or change a record because a related record is required in table Job Order (main).

Do you know what I mean?
If not just ask me what you don't understand...

Do you want me to send the codes to you?

I hope you can help me....
Thanks in advance.

 

[johnwm]

In your cmdSave button code in the JobGrid from you will need to pick up the Jo_no from the Job Order form as well as the JobGrid data. If your Job Order Grid form is already showing Jo_no (eg in a text box) then use that.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[pingpang]

Hi again! thanks again for replying.

I've tried that already. But an error occured. (Run time error '-2147467259 (80004005)': You cannot add or change a record because a related record is required in table Job Order (main).

I think you can't save a jo_no from the job order grid form since it is a foreign key referencing the jo_no of job order form. Since there is no existing jo_no from the job order form, that's why it resulted an error. My problem is I can't solve that problem...

I hope that you could give me suggestions.

Thanks in advance

 

[johnwm]

When you generate the new jo_no in your second form, save that record in the job table before moving to the jobgrid form
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[pingpang]

yeah, that works... thanks!

My idea is that I would set the datagid visible=false when you are going to add a record in the jod order form. and when I click save the datagrd visible-true. My problem is when I click the save button, there is an error. I know what the error is, but I can't find any solution as of the moment. Since, the job order form contains a combobox for a lists of customer name, I can't figure how to code on the save_click procedure that would save the field c_id on my job order table. Remember my 3 tables.

I hope you understand what I mean.

Thanks again.

 

[johnwm]

The selected value in a combobox control can be seen like this:

msgbox Combo1.List(Combo1.ListIndex)

Do you only have Customer names in your combobox?

If so, when you loop through your customer database to fill the combobox put your c_id in the itemdata field of the combobox:

With rs
Do While NOT rs.EOF
Combo1.AddItem rs.Fields("Name&quot
Combo1.ItemData(List1.NewIndex) = rs.Fields(c_id)
Loop
End With

Then when you save your jobcode get c_id like this:
rs!c_id = Combo1.ItemData(Combo1.ListIndex)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[pingpang]

I almost solve the problem already...
I have another question to ask. How can you get the sum of a column from a datagrid?

I have an idea though, it goes something like this

Dim i As Integer
For i = 1 To Adodc2.Recordset.RecordCount
txtjo_total.Text = Val(txtjo_total.Text) + Val(DataGrid1.Columns(4).Value)
Next i

Is this correct? and what event will I put the code?

 

[pingpang]

Oh, I forgot to say thanks!

 

[pingpang]

Oh, I forgot to say thanks! Thank YOU.