Datagrid

KennyMac
01-09-2004, 02:57 PM
I'm using a datagrid to add new records based on a foreign key ID
I create the Foreign key ID and then place it in the Query. This creates the first record. That works fine however i cannot add multiple records to the datagrid all of which have the same foreign key ID.

Here is the code that i am using to add multiple records:

Private Sub cmdAddComponents_Click()

Dim SQLComponents As String

On Error Resume Next
SQLComponents = "Select * from qryComponent where Machine_id =" & intMachineID
'this will select the ID which has already been placed in the query from a
'previous command

Set rs = New ADODB.Recordset
With rs
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open SQLComponents, adoConnection, adOpenDynamic
End With

'I want to add multiple records here
'I know that the .addnew is wrong
'it just clears the original ie(i can only enter one record)
rs.AddNew
rs("Machine_id") = intMachineID
rs.Update

Adodc1.RecordSource = SQLComponents
Call Adodc1.Refresh
DataGrid1.Visible = True
End Sub


Can anyone help me???

airski
01-09-2004, 09:30 PM
What are the other records you are wanting to enter? Where do they come from?

In your sql you say "where Machine_id =" & intMachineID" and then you also try to add a new record with "rs("Machine_id") = intMachineID"??

KennyMac
01-10-2004, 05:01 AM
What are the other records you are wanting to enter? Where do they come from?

In your sql you say "where Machine_id =" & intMachineID" and then you also try to add a new record with "rs("Machine_id") = intMachineID"??

Basically i have two tables Machine and component. Each machine has one to many components. First i enter the machine details, this works fine. I take the machine id that is generated and place it in a Form level variable call intMachineID. Then i want to create multiple records in the components table for this intMachineID. So i have a datagrid connected to an ADO that is connected to the query qryComponents. I create an inital record in the datagrid using the intMachineID. I can't think of any other way to create this records in the datagrid. intMachineID is the foreign key so it has to exist.
I'm using a query rather than the table because i believe it is more stable on the Access side. I hope this explains it a little better. Here is the code for both commands i'm using; Save Machine(cmdWrite) and Add Components(cmdAddComponents)


Private Sub cmdWrite_Click()

Dim strMachineName As String
Dim datPurchaseDate As Date
Dim curPurchaseAmt As Currency
Dim dblDepLevel As Double

strMachineName = txtMachine_name.Text
datPurchaseDate = txtPurchase_date.Text
curPurchaseAmt = txtPurchase_amount.Text
dblDepLevel = CDbl(txtDep_amt_year.Text)

Dim SQLMachine As String

SQLMachine = "SELECT * FROM qryMachine"

Set rs = New ADODB.Recordset

With rs
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open SQLMachine, adoConnection, adOpenDynamic
End With

rs.AddNew
rs("Machine_name") = strMachineName
rs("Purchase_date") = datPurchaseDate
rs("Purchase_amount") = curPurchaseAmt
rs("Depreciation_amtperyear") = dblDepLevel
rs.Update
intMachineID = rs("Machine_id")


rs.Close

MsgBox "Details written successfully"

Dim SQLComponent As String

On Error Resume Next
SQLComponent = "Select * from qryComponent"
'DataGrid1.Visible = True
Set rs = New ADODB.Recordset

With rs
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open SQLComponent, adoConnection, adOpenDynamic
End With

rs.AddNew
rs("Machine_id") = intMachineID
rs.Update

End Sub




Private Sub cmdAddComponents_Click()

Dim SQLComponents As String

On Error Resume Next
SQLComponents = "Select * from qryComponent where Machine_id =" & intMachineID
'this will select the ID which has already been placed in the query from a
'previous command

Set rs = New ADODB.Recordset
With rs
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open SQLComponents, adoConnection, adOpenDynamic
End With

'I want to add multiple records here
'I know that the .addnew is wrong
'it just clears the original ie(i can only enter one record)
rs.AddNew
rs("Machine_id") = intMachineID
rs.Update

Adodc1.RecordSource = SQLComponents
Call Adodc1.Refresh
DataGrid1.Visible = True
End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum