very confused about using recordsets vs. queries or both? 2

[trayb]

I'm sort of new to Access and I'm having some trouble understanding when to use queries vs. recordsets vs. SQL etc. to update table data.

I'm building a database that will include tabbed pages with linked subforms on each page. The subforms are based on (multi-table) underlying queries, which I assumed would then update the corresponding underlying tables as the user input form data. Is this right?

But it doesn't end there... I also must integrate code which will capture an autonumber from the mainform and display it on a subform, as well as update it to the (subform) table as a foreign key when the record is saved. Then I must do the opposite: save the subform (table) autonumber to the mainform table? Is this even possible?

For the 1st scenario, I tried using the code below (to no avail):

Private Sub btnSaveProp_Click()
On Error GoTo Err_btnSaveProp_Click
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL, strFldData As String

'capture subform data
strFldData = Str(Me![PropID])

'not sure about the following syntax but I only
'want to insert into 1 data field & the whole
'record is huge so I only selected necessary data

strSQL = "SELECT JobID, PropDetailID FROM
Jobs WHERE JobID = " & Str(Me.Parent![JobID])

'can i execute 2 different sql statements? how?
strSQL2 = "INSERT INTO Jobs (PropDetailID) Values
strFldData)"

If Not IsNull(JobID) Then
Set db = CurrentDb.OpenRecordset(strSQL)
Else
MsgBox "There was a problem adding data"
End If

rs.Close .

Exit_btnSaveProp_Click:
Set rs = Nothing 'Deassign all objects.
Set db = Nothing
Exit Sub

Err_btnSaveProp_Click:
Resume Exit_btnSaveProp_Click
End Sub

How do you integrate a SQL statement with a form/underlying-query modification? I know I've got this all mixed up somewhere, can anybody help me get straightened out? Thanks for any help you can give.

 

[scriverb]

It sounds like you have a one-to-many relationship between a Master table and a number of Child tables. When creating the Master Form the key field that links the Master Table to the Child Tables must be created as a control. Then in the subforms that are created also make sure that the field that links the Child record to the Master is bound to a form control.

When this is done just the creation of the SubForm object with the underlying subform for the Child Table will the facilitate the linking process. The properties of the main forms subform object has two properties that perform this task for you automatically. Link Child Fields and Link Master Fields. By clicking into either of these and clicking the ... code button you will be presented with a form that requries you to identify the Master Link Field control and the Child Link Field control. Pick from the pick list the control on the Master that identifies the master as unique and also pick the child field where you want this value to be updated in the Child form/table. This is done automatically when you create a new record in any of your subforms and does not require any additional code to keep it straight. You also do not need to pass any identifying data from the Child Record to the master form/table to further enhance this linking process.

Let me know if this totally confuses you and maybe I can explain it a little better. Bob Scriver

 

[FancyPrairie]

I'm not sure what you're trying to do. scriverb explained pretty well how to handle the subform. However, based on your title, you are also confused about coding, recordsets, etc. I don't know if this will help (may give you some ideas), but here goes...

Simply speaking, you set the RecordSource property of forms (subforms) and reports to either a table, a query (that you build with the query builder), or a SQL statement that you hardcode as the recordsource (i.e. "Select * from tblCustomers;&quot. I prefer to set the RecordSource to a query. Now you can add the fields (contained in the RecordSource) on your form. Note that a form is based on a Recordset (Me.Recordset)

You can think of Recordsets as a set of 1 or more records. Recordsets are used most frequently in code to move through the records or find items with in records. Recordsets, like forms, are based on tables, queries, or a SQL statement.

One of the first things you should learn is how to use the debugger. It will help you figure out what your code is doing. Here's a few things you should know to get you started.

1. Insert the word STOP in your code prior to the line of code you want to examine. (You can also place the cursor on the line of code and toggle the breakpoint (button located on toolbar)).
2. Execute your code
3. Access will stop executing the code at the breakpoint or when it encounters the word STOP.
4. Place the cursor over a value you want to examine. Access will diplay the value it contains. Or, in the Debug Immediate window type ?txtJobID. Access will display the value of txtJobID.
5. Press F8 to step thru your code 1 line at a time.
6. Press F5 to continue executing your code until it reaches the end or it encounters another STOP or breakpoint

The code in your post was using DAO. Access is moving away from that and using ADODB. So your code should look something like this:

Private Sub btnSaveProp_Click()
    
    On Error GoTo Err_btnSaveProp_Click
    
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset          'You don't seem to be using this
        
    Dim lngAffected As Long             'Indicates # of records inserted (not necessary)
    
    Dim strSQL As String                'You had this declared as a variant (implied)
    Dim strFldData As String            'You don't seem to be using this
    
    Set cnn = New ADODB.Connection
    cnn = CurrentProject.Connection
        
'***************************************************************
'*  Don't know what you want to do with this Select statement  *
'***************************************************************

    strSQL = "SELECT JobID, PropDetailID FROM Jobs WHERE JobID = '" & Me.Parent![JobID] & "'"
    
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
    
'************************************************************************************
'*  This line of code will excute the SQL Insert statement                          *
'*  (Note the single quotes)                                                        *
'*  (Also note BeginTrans, CommitTrans, and Rollback.  If the Insert works with no  *
'*  errors, then the data is commited (saved).  Else nothing is saved (rolled back) *
'************************************************************************************

    cnn.BeginTrans
    cnn.Execute "INSERT INTO Jobs (PropDetailID) Values '" & Me!ProdID & "'", lngAffected
    cnn.CommitTrans
    MsgBox lngAffected & " records were inserted."
    
'***********************************************
'*  Don't know what you are trying to do here  *
'***********************************************

    If Not IsNull(JobID) Then
        Set db = CurrentDb.OpenRecordset(strSQL)
    Else
        MsgBox "There was a problem adding data"
    End If
      

'********************
'*  Exit Procedure  *
'********************

Exit_btnSaveProp_Click:
        
    On Error Resume Next
    
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
        
    Exit Sub

'********************
'*  Error Recovery  *
'********************

Err_btnSaveProp_Click:

    MsgBox Err.Number & vbCrLf & Err.Description
    
    cnn.RollbackTrans
    
    Resume Exit_btnSaveProp_Click

End Sub

 

[KenReay]

Hi

Are you using Bound Forms?

If yes, the updating of the tables is done for you.

When using Subforms (on bound Forms), if you set the link fields property correctly on the subform control, then Access will automatically pass the linking field(s) to the sub form (to answer your question about the Autonumber / Subform).

I did not examine your code in detail, since if you are using bounds forms, you should not need most (or even all) of that code, but I did notice some errors eg:

strSQL = "SELECT JobID, PropDetailID FROM
Jobs WHERE JobID = " & Str(Me.Parent![JobID])


If JobId is defined as numeric in the atble definition you need:

strSQL = "SELECT JobID, PropDetailID FROM
Jobs WHERE JobID = " & Me.Parent![JobID] & ";"


If it is defined as a string:

strSQL = "SELECT JobID, PropDetailID FROM
Jobs WHERE JobID = '" & Str(Me.Parent![JobID]) & "';"

I have left your str() in, but I am not clear why it would be needed, since Me.Parent![JobID] is presumably the same type as JobId in the table? (ie a string in the example)
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[scriverb]

trayb: My response concerning the subforms did not address your VBA code as it looked as if you were trying to update the linking fields between both the master table and the child table. Your statements let me to believe that you were confused about these relationships and that is why I attempted to address those issues and explain you only needed to address the forms linking properties and did not require the vba code that you displayed. Bob Scriver

 

[trayb]

Thanks so much for the responses, I gained something from ALL of them.

So what I've gathered so far is that if I have a primary key [JobID] in my master (one) table that's serves as a foreign key in the child (many) table, then that relationship alone is sufficient right? And if I link these fields as master and child on the mainform and subform accordingly, then [JobID], for instance, will automatically be saved in the child table? awesome...

I started out using ADO vs. DAO and kept getting those reference errors so I moved DAO up in the heirarchy and started using it instead. Bad move? Also, thanks for the debugging tips. I was struggling with that somewhat.

Final question: when is it appropriate to use SQL as a recordsourse instead of tables or queries? Is it when you can't accomplish something with a table or query? Is it ever used in conjunction with the other recordsources? And if so, under what circumstances would you do so?

Sorry to sound like such a dimwit but the help files don't offer much for these kind of conceptual questions. Thanks again for the help!

 

[KenReay]

Hi

Using SQL vs using a query is really a none question.

A Query is just saved SQL, so they are effectively the same thing.

Similary essenatially in the terms we are talking about it, a table is just a very simple query (ie SELECT * FROM tblName so again there is not difference in concept.

It is generally best to use saved queries, but if you want to build the SQL string 'on the fly' then you would use SQL string. That is my general rule of thumb.

Hope that helps
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[scriverb]

KenReay has given you an excellent explaination of SQL vs Query. I would add one thing. Queries have the advantage of being a compiled SQL. This means that the Jet database engine has Optimized the SQL by analyzing your tables, relationships, indexes, etc. and has chosen the best way to organize the query and has saved it in a compiled state. After creating a query in design mode run it one time to initiate the Optimizer and then save it. Each time you modify the query rerun the query one time to perform this function again otherwise each user will experience a slight slowdown the first time that it runs. It will run in its most effecient manner now.

When you build SQL in code it will not be compiled and will not utilize all the power of the query optimizer.

Bob Scriver

 

[trayb]

Thanks guys. Finally I get it.