Better way of adding records by code?

[Mudface]

I've cribbed the following to insert a list of questions from my tblQuestions into tblInterviews based on the RefNo in tblMain. The code runs from a command button on the subform fsubInterviews on frmMain. It works OK, but just looks and feels a bit 'clunky'. Is there a better or more accepted way of doing this sort of thing? Cheers for any help.
<pre>
Public Sub AddQuestions(strRefNo As String)
Dim db As Database
Dim rsIncident As Object
Dim rsQuestions As Object
Dim rsInterview As Object
Dim strSQLMain As String
Dim strSQLQuestions As String

Set db = CurrentDb

'Check if RefNo already exists

strSQLMain = "SELECT * FROM tblMain WHERE RefNo = '" & strRefNo & "'"
Set rsIncident = db.OpenRecordset(strSQLMain, DB_OPEN_DYNASET)

If rsIncident.RecordCount = 0 Then
rsIncident.Close
MsgBox "The incident with ref number " & strRefNo & " could not be found!" & vbCrLf & _
"Unable to generate questions.", vbCritical, "Error"
Exit Sub
Else
strSQLQuestions = "SELECT * FROM tblInterviews WHERE RefNo = '" & strRefNo & "'"
Set rsQuestions = db.OpenRecordset(strSQLQuestions, DB_OPEN_DYNASET)
If rsQuestions.RecordCount <> 0 Then
rsQuestions.Close
MsgBox "An interview has already been conducted for this incident!" & vbCrLf & _
"To regenerate the question list, delete all records below", vbCritical, "Error"
Exit Sub
End If
End If

' Add questions

Set rsInterview = db.OpenRecordset("tblInterviews", DB_OPEN_DYNASET)
Set rsQuestions = db.OpenRecordset("tblQuestions", DB_OPEN_DYNASET)
rsQuestions.MoveFirst

Do
With rsInterview
.AddNew
!QuestionID = rsQuestions!QuestionID
!RefNo = strRefNo
.Update
End With
rsQuestions.MoveNext
Loop Until rsQuestions.EOF

rsQuestions.Close
rsInterview.Close
db.Close

End Sub
</pre>

 

[Ian Mac]

Mudface, long time!

I know a man who does, if your still looking email me, I can't get in here very often.
My man is a wizard at SQL ran via code.

Till later.

 

[Mudface]

Cheers, Ian, I think I've got it sorted now, though. I can replace some of the first part of the code using DCounts and possibly alter the structure a bit so it makes a bit more sense. I nicked the code from another database created for us by an external contractor. He left a really bad impression for sloppy work, so I thought it best to check anything that he's done first .