Unbound Forms/ADO/VBA 1

[ParyGoal]

I already posted this question in a different forum, but I got not answer. I am hoping someone will be kind enough to help me.
I am trying to create/enter records in my access database with a parent child relationship. I have several unbound subforms. Each subform reprents rating for the corresponding section (Category).
I do not want to create one subform and link it to the parent form and let access handle the recording/editing of records.
I would like to accomplish this through ADO recordset when entering records and viewing them by selecting a user name from a combo box on the parent form. Each user will be evaluated on specific catgory. I just don't know how to accomplish this through VBA. I don;t know how access allows you to enter records in the Child Table through the form-subform linked though the primary key and foriegn key. I think one should be able to accomplish the same thing through VBA? Does anyone have any example that uses unbound form within a parent form that users VBA to add and view records? Is it possible? I hope I was clear with my question
Any help will be greatly appreciated
Thank you
Parygoal

 

[cal555]

I am not sure if this will answer all of your question here, but hopfully it will put you in the right direction.
I have a form with a subfor on it, and this is how I handle it.

Private Sub cmdEnter_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim strSqL1 As String
Dim strSqL2 As String
Dim d, s As Long
Dim Ord As Long

strSqL1 = "Select OrderID AS d from orders" 'This stores the query in variable
strSqL2 = "Select * from [Orders Detail]"

Set cn = CurrentProject.Connection ' This establishes the connection

Set rs = New ADODB.Recordset ' This sets the records set
Set rs1 = New ADODB.Recordset ' This sets the records set

rs.Open strSqL1, cn
rs1.Open strSqL2, cn

On Error GoTo Err_MSG

If rs!d = 0 Then

Err_MSG:

If Err.Number = 3021 Then

'This inserts a new record into the Orders database
strSqL2 = "INSERT INTO orders([OrderID],[OrderDate]) VALUES('" & CLng(txtOrderID) & "','" & txtOrderDate & "')"
cn.Execute strSqL2

'This inserts a new record into the Orders Detail database
strSqL2 = "INSERT INTO [Orders Detail]([OrderID],[ItemID],[Quantity],[OrderDate]) VALUES('" & CLng(txtOrderID) & "', '" & CLng(txtItemID) & "', '" & CLng(txtQty) & "','" & txtOrderDate & "')"
cn.Execute strSqL2

End If

Exit Sub

End If

'This inserts a new record into the Orders Detail database
strSqL2 = "INSERT INTO [Orders Detail]([OrderID],[ItemID],[Quantity],[OrderDate]) VALUES('" & CLng(txtOrderID) & "', '" & CLng(txtItemID) & "', '" & CLng(txtQty) & "','" & txtOrderDate & "')"
cn.Execute strSqL2

cn.Close

txtOrderID = ""
txtItemID = ""
txtQty = ""
txtOrderDate = ""


Set cn = Nothing
Set rs = Nothing
Set rs1 = Nothing
rs.Close
rs1.Close

End Sub

Private Sub cmdOrderDetails_Click()
DoCmd.OpenQuery "qryOrderDetails"
End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub Form_Open(Cancel As Integer)
txtOrderDate = Date
End Sub

Hope it helps

 

[ParyGoal]

Hi cal555. Thank you so much for your reply. I appriate the code vey much.
Now suppose we have the same database structure, could I use a single form as the default view for the subforms instead of using continious or datasheet view, and by the same token be able to update the tables and view the info? Will your code handle this situation? Again I am not binding the subform the parent form.
Thank you
Parygoal

 

[cal555]

I am not sure, without talking to you more to see what you mean by a dfault view. However, the code I sent you In volved a form and subforms and they are only connected to the database throught the ADO code. And I have found that there is not much you can not do if you do it throught ADO code, but there si a lot more to ADO code than what I have above. For example, through the connection you can make a Select statement obtain data form the fields of a table, in a database, and go to any controlls you want on any form. You just have to know what you want to happen and know how to write ADO. You might want look on the web for articals on ADO coding I know there are tutorials out there. But with this you really can do fare more than Access offers on its own.
Hope this helps

 

[ParyGoal]

Thank you Cal555 for your replay. I really appreciate it.
I am not sure I was clear with my question.
For example, using your code above to handle the parent and child records. And assuming that my subform is in default fault view not in continous or datasheetview, how would you display the detail records once you make a selection from a combo box located on the parent form? I guess my confusion has to do with looping through the detail records (via ADO) based on the parent key selected on the parent form. I know access can handle it for you if you link your subform to the parent form through, but I do not want to use this option. In my particular situation I have several unbound subforms (25) all of which will be based on the detail table.
Thank you in advance
Parygoal

 

[cal555]

Hi,
Think I understand a little better what you are looking for and I will try to together an example and post it to night here.

 

[cal555]

Hi Here is what I did to solove your problem. Now I put a comboBox on one form and a text box on another form. The combo box gets a list of customers from the customers table through Sql statement. It then assigns it to a a global var that I defind in modual so that I can access it wany where in the program. then in the other form when I hit the camand button it assigns to the text box the value in the global variable. Now as far as your reference to parent child relationships. I am not sure where that comes into play here. I am not a data modeler, but I think with referential integrety rules, you have to make sure that additions, updates, and delets are performed on the the foreign key table first( child table). In any case here is what I did to make this work:
In the form with the combo box.

Option Compare Database

Private Sub cboCust_AfterUpdate()

GLBHoldSel = Me!cboCust.SelText
End Sub

Private Sub cboCust_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Click()


End Sub

Private Sub Form_Load()

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim str As String

Set cn = CurrentProject.Connection ' This establishes the connection

Set rs = New ADODB.Recordset ' This sets the records set

str = "Select * from Customers"

rs.Open str, cn

' This will load the combo box
Me!cboCust.RowSource = "Select * from Customers"

End Sub

The in the form with the text box:

Option Compare Database

Private Sub Command3_Click()
txtUserSelection = GLBHoldSel
End Sub

Private Sub Detail_Click()

End Sub

 

[ParyGoal]

Thank you so much cal555 for the code.
I have just a question for you. What if you have let's say three test boxes or several on the subform do have to create global variables for each? I guess I am confused about how to handle history records, and display them on the subform. Let's say UserID=12 has to history records in the detail table. How should I go about display the history records as well as saving new history records and at the same time maintain the relationship (Primary key----> Foriegn Key)
Here an example:
Parent Table:
EvalutionID Pk Autonumber
UserID
UserName
EvaluationDate

Child Table:
EvaluationDetailID Pk autonumber
EvalutaionID fk number
EvalatedbyID number



The EvalutionID is key that points to a lookup table that tells me what criterias were selected.

I hope I was clear with my example and questions
Thank you again
parygoal

 

[cal555]

No you would just take the ID from the global variabe and use that in a SQL statement. It al depends on what you are really doing. I am still not sure that I am seeing the whole pitcher here. Like I do not see what you mean bey history records here. As far as the insert statement goes, you can go something like this:

Private Sub Command2_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim str As String

Set cn = CurrentProject.Connection ' This establishes the connection

'This inserts a new record into the Parent database
str = "INSERT INTO tblParent([UserID],[UserName],[EvaluationDate]) VALUES('" & Text0 & "', '" & Text3 & "','" & Text5 & "')"
cn.Execute str

End Sub

Here you will have to insert a record in the primary ket table first before you insert into the foreign key table.

And, I will suggest again, that you again you need to get a good book on ADO, because many things you can run into; like formating, or DML functions you need. Then even if you know the code how to incorperate it in Access is a whole other thing. Im really VB developer, which is more complex, but in some ways easer to use then Access.
Hope this helps.