marsh (and ken), this seems to be doing it
as usual, seeing a little success starts me thinking and when that happens
it can get 'dangerous'....i added a few more fields that i thought could be
'defaulted' but when i came to a binary list field ("Continuing") on my form
("Yes";"No") which is coded on the form/table as having "No" be its default
things got 'interesting'. the error i get when i add it (using the code i'm
pasting immediately below) reads:
91 = Object variable of With block variable not set
in my Dim statements this is the 'cnt' field i dimmed as "Listbox". is that
my doing or in the nature of list fields and their relation to SQL commands?
Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer
Dim db As Database
Dim pid As Long
Dim prt As String
Dim unt As String
Dim PRN As String
Dim med As String
Dim cnt As ListBox
Dim rs As DAO.Recordset
Set db = CurrentDb
pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1
pid = DLookup("ProtocolID", "tblDefaults")
prt = DLookup("ProtocolTitle", "tblDefaults")
unt = "mg"
PRN = "No"
med = "At baseline"
cnt = "No"
sql = "INSERT INTO [Concomitant Medications] ([Protocol ID],[Protocol
Title],[Patient Number],[Med Number],[Unit],[PRN],[Medication
Taken],[Continuing])" & _
"SELECT " & pid & " , """ & prt & """ , " & pn & " , " & mn & " , """
& unt & """ , """ & PRN & """ , """ & med & """ ,""" & cnt & """ ;"
MsgBox sql
db.Execute sql, dbFailOnError
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]= " & pn & " and [Med Number] = " & mn & " "
If rs.NoMatch Then
MsgBox "something's wrong"
Else
Me.Bookmark = rs.Bookmark
End If
Exit_Add_Record_DblClick:
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick
End Sub
:
It's one of those "goofy" instances. Actually, the
situation is very common since its the right way to deal
with text fields.
yadeyadee & ", """ & prt & """ , " & yadeyadee
or alternatively, as you had it inappropriately for the
numeric values before:
yadeyadee & ", '" & prt & "' , " & yadeyadee
but the latter is more likely to fail if the prt value
contains an apostophe.
--
Marsh
MVP [MS Access]
Ted wrote:
can you give me the syntax. what would it look like for the prt variable?
something like yadeyadee ", " & "prt" & " , " yadeyadee....? or is it one
of those 'goofy' instances where you need to have doublequotes nested within
other doublequotes or something like that? one thing, i don't want the text
to appear with quotations around it in the actual table. hope that can be
avoided.
Ted wrote:
i just thought i'd add a "MsgBox sql" line in my vba and it displayed the
contents of the INSERT INTO text. looking at it, i have this gut impression i
know what the problem is. my instinct is is that there's an innocent comma
(",") in the title which is confounding things. am i getting warmer? if i'm
not off base w/ my hunch, how would we tell vba to ignore this comma?
:
Excellent idea wrt to using a MsgBox. I think you
definitely spotted something important there, but you are
misinterpreting the correction. The title is a Text type
field, so this one requires quotes around it (unlike the
nemeric fields).
