Formatting of Insert Command

[reeseburg]

I'm working on a database that will review patient records with a series of questions. I have a form for a new review that contains a subform with the questions. The questions are linked to the review id, so when generating the questions, I need the insert the current review id into each new record in the subform's database.

When I run this code, it doesn't recognize the control on the main form that contains the current review number and just doesn't insert records into the tblanswers table. When I print the current review number in the Immediate window, it properly identifies the current review.

' Add data to new records, pulling Review_ID control in Main Form
dbs.Execute "Insert into tblAnswers " _
& "(Reviews_ID, Question_id) " _
& "Values (Forms![frmChart Reviews]![Review_ID].Value, " _
& "" & IntegerCounter & ");"


I receive the Microsoft Visual Basic error:
Run-time error '3061':
Too few parameters. Expected 1.

I think it is pointing to the last line.

Obviously, I'm new to VBA...

 

[vbaInet]

You expose the reference to the control the same way you exposed the IntergerCounter variable:

    dbs.Execute "INSERT INTO tblAnswers " & _
                    "(Reviews_ID, Question_id) " & _
                "VALUES (" & Forms![frmChart Reviews]![Review_ID] & ", " & _
                     IntegerCounter & ");"

 

[spikepl]

... and don't do too many things at once:

Dim strSql
strSQL ="INSERT ...."
debug.print strSQL ' to be able to inspect it, so as to see what exactly goes into the Execute
dbs.Execute strSQL

 

[vbaInet]

And some validation to ensure the control actually contains something will be a good thing to implement.

By the way, what's the idea behind this? Are you not using a bound form?

 

[reeseburg]

Your recommended code works and is incorpoartated.

The intent is to create a list of review records for chart reviews. tblanswers are linked to tblreviews and tblquestions (so I can change the questions and use the record reviews for different areas of concern).

When the user opens the form "frmChart Reviews", they enter the patient data, date of review, unit patient admitted, and name of reviewer. The database is normalized (sorry-American spelling). What I'm doing is creating a recordset of questions to be answered in the subform for that review. Right now, that routine begins with a button click (cmdBeginReview_Click())

I am quite certain that there is an easier way, but this method works (very painful process to develop it). It actually doesn't end up refreshing the form to show the questions unless Refresh is pressed, so it's less than perfect/ideal.

Here's the subroutine altogether:

Public Sub cmdBeginReview_Click()
Dim strMsg As String
Dim iResponse As Integer
Dim reviewNumber As Integer


If gcfHandleErrors Then On Error GoTo PROC_ERR

If gcfHandleErrors Then Debug.Print "The Form number is: " & Forms![frmChart Reviews]![Review_ID].Value

' Check to see if records for this review exist
If DCount("[Answer_ID]", "qryAnswersExtended", "[Reviews_ID] = Forms![frmChart Reviews]![Review_ID].Value") > 0 Then

MsgBox "This record exists. You can begin the audit below.", vbOKOnly + vbExclamation, "Record Found"
GoTo PROC_EXIT

Else

End If

' Go to Subroutine to create the tblAnswers records for this review
cmdSetupQuestions
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume PROC_EXIT

End Sub
Public Sub cmdSetupQuestions()
Dim IntegerCounter As Integer
Dim dbs As Database
Dim Msg, Style, Response As String
Dim strSQL As String


If gcfHandleErrors Then On Error GoTo PROC_ERR
Set dbs = CurrentDb()

' *** Add count function to determine how many questions
' *** Add loop through types of questions?

IntegerCounter = 24 ' Initialize counter

Do While IntegerCounter < 45 'Loops through
' active questions in tblQuestions

' Add data to new records, pulling Reviews_ID from frmChart Reviews
strSQL = "Insert into tblAnswers " _
& "(Reviews_ID, Question_id) " _
& "VALUES (" & Forms![frmChart Reviews]![Review_ID] & ", " & _
IntegerCounter & ");"

Debug.Print strSQL ' show value for record created

dbs.Execute strSQL

IntegerCounter = IntegerCounter + 1
Loop

Me.tblAnswers_subform.Requery
Me.Requery

' *** (need to do) Insert reference to current record and return there after refresh -- use [review_id] as reference

Application.RunCommand acCmdRecordsGoToLast

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume PROC_EXIT

End Sub

 

[reeseburg]

Working on the validation piece. If it's a new record, wouldn't the autonumber primary key field "Review_ID" in [tblRecord Reviews] be null? I then check to see that the record is not null (entry of any data in any of the form's controls creates a new record and subsequently, a valid Review_ID number for creating the answers fields needed.

Tim

 

[vbaInet]

If you're adding a new record using the recordset it will return the next autonumber.

By the way, if this is going to be used by multiple users, you will need to populate a local table. I will need to see the process in action to be able to advise.

 

[reeseburg]

It is populating a local table.

Best way of showing you the process?

 

[vbaInet]

You can upload it here:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

 

[reeseburg]

Here is the database in all of its unglory.

 

[vbaInet]

So what are the steps to kick start this thing?

 

[reeseburg]

Press Record Review on Main Menu.
Select any of the fictitious patients or reviewers, then press "Begin Review".

 

[vbaInet]

When I first opened your form I thought you had a timer running because that textbox kept on flashing. Did you notice that? The problem you had is called Circular Referencing and if you look left of the Status Bar it constantly reads "Calculating...". This is what your expression should be:

=IIf(IsNull([Review_ID]),Null,DCount("[Answer_ID]","qryAnswersExtended","[Reviews_ID] = " & [Review_ID]))

 

[reeseburg]

It isn't doing it on my screen. Which is the circular referencing line(s) of code? Is this the error check for a blank Review_ID field?

Tim

 

[reeseburg]

Got it. One more question.

How do I get the answers to show up in the subform without pressing the Begin Review button twice? (when entering a new review record)

 

[vbaInet]

It isn't doing it on my screen. Which is the circular referencing line(s) of code? Is this the error check for a blank Review_ID field?

Tim

The code you had in that textbox previously. Besides the IIF() and IsNull() functions I used, compare the DCount() part with yours.

 

[vbaInet]

Got it. One more question.

How do I get the answers to show up in the subform without pressing the Begin Review button twice?

If this question isn't related to your Original Post, you will need to create a new thread for this.