This one has had me tearing my hair out for ages but the solution still eludes me. And my last 15 years before retiring were working with Access & VBA, so my pride is hurting even more than my hair!
The project:
A database to catalogue portraits recording for each artist name, sitter name, date painted (when known), brief details on content (may be none), and the portrait’s location (if known). In addition to the table for portrait details are a table for artist information and one for sitter information (plus various lookup & other tables that aren’t relevant to this issue).
My client was super-keen to get data entered so she uses the memo field on the artist form (the first one designed) to enter details of portraits painted by that artist – following a strict pattern set by me (and checked before attempting to process). That’s not the issue – my parsing routine correctly identifies the data to be processed for the creation of the new sitter records and portrait records: I know this to be true because my code is liberally scattered with message boxes showing the VBA’s interpretation of the data (vital for debugging!)
The process of parsing + posting is briefly as follows (the memo field is assigned to a variable which is then parsed one character at a time):
- The sitter’s name is identified (always the first entry on a new line) and assigned to a variable, then (if relevant/if known) brief details, date painted & location;
- once it’s reached the end of the details for the portrait it shows a message box giving that information as interpreted by the routine for me to confirm;
- it then appends the sitter’s name to the sitters’ table and returns the ID of the new record. This works. It then appends (or, rather, it should append but doesn’t!) the relevant data, including the sitter ID (and the artist ID from the form) to the portraits table.
- It then continues (on that line if there are more portraits for that sitter, otherwise on the next line, until it reaches the end of the memo).
Originally the two append processes were by dynamically-created SQL – building the SQL strings using the appropriate variables – using database.Execute. But when I found that the second append was failing – with no error messages – I spent several hours looking at various tech sites, and one message was coming through strongly – use parameterised queries! OK, hadn’t used these before (not in VBA – of course I’d used them on forms for select & other queries) so I set up a simple test database to mimic the process but without putting my client’s data at risk!
It took a while – and a bit more time online – before I got it right, but yay, it worked with all combinations of missing data. (Incidentally, as you’ll note from the table defs below, the “year painted” is an integer field, which of course doesn’t accept “Nulls”, and I don’t want a zero where there’s no date, so there are two append portrait queries, one omitting posting to the ‘year’ field.)
And now I’ve gone back to the live database – and the query that appends the sitter is working, but the other two aren’t, again with no error message. So it’s over to you, please!
TABLE DEFS (I’m only listing the relevant fields; no fields have Required set to Yes; zero-length strings allowed):
taArtists: arID – autonumber; arNotes – memo; (plus other fields)
taSitters_Sub: ssID – autonumber; ssFullname – text 70; (plus other fields)
xtaPortraits: xrID – autonumber; xrArtistRef – long integer (link to taArtists = arID); xrSitterRef – long integer (link to taSitters_sub = ssID); xrPortraitName – text 25; xrLocationCode – text 20; (plus other fields)
PARAMETERISED QUERIES qu_app_sitter: INSERT INTO taSitters_Sub ( ssFullname ) SELECT [par1] AS Expr1;
qu_app_portrait: INSERT INTO xtaPortraits ( xrArtistRef, xrSitterRef, xrYearPainted, xrPortraitName, xrLocationCode ) SELECT [par2] AS Expr1, [par3] AS Expr2, [par4] AS Expr3, [par5] AS Expr4, [par6] AS Expr5;
qu_app_portrait_NoYear: INSERT INTO xtaPortraits ( xrArtistRef, xrSitterRef, xrPortraitName, xrLocationCode ) SELECT [par2] AS Expr1, [par3] AS Expr2, [par5] AS Expr4, [par6] AS Expr5;
EXTRACTS OF VBA
(Were I to start again I’d probably feed the values for par2 to par6 to a separate subroutine but while that would reduce the amount of code I’m not sure it’d actually be more efficient! I am, of course, open to expert advice on that! Were my client to start again I’d get her to wait for the system to be complete before entering data – making all this redundant – or get her to enter the data in an Excel spreadsheet & I’d process it from there. One lives, one learns [hopefully!])
1 – declarations, setting database & querydefs: [only showing relevant Dims:
Private Sub Command77_Click()
On Error GoTo myError
Dim myID As Long 'the ID for the painter
Dim myName As String 'the name of the sitter
Dim myDesc As String 'any text description **could include part-dates or other digits
Dim myLoc As String 'the location code
Dim myDate As Integer 'myNum converted from string if it's 4 digits
Dim Errline As Integer ‘used so error messages get me close to the problem
'**** Now the database stuff
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mySitterID As Long, myNewRows As Integer
Dim qd1 As DAO.QueryDef, qd2 As DAO.QueryDef, qd3 As DAO.QueryDef
Dim par1 As DAO.Parameter, par2 As DAO.Parameter, par3 As DAO.Parameter, par4 As DAO.Parameter, par5 As DAO.Parameter, par6 As DAO.Parameter
'**** Set these once, and turn off in 'Leave'
Set db = CurrentDb()
Set qd1 = db.QueryDefs("qu_app_sitter")
Set qd2 = db.QueryDefs("qu_app_portrait")
Set qd3 = db.QueryDefs("qu_app_portrait_noyear")
2 showing routine calling the insert queries plus preceding message box [there are 9 of these, similar but not identical, depending on missing information]
MsgBox "Got name & location only" & vbCrLf & vbCrLf & "Sitter: " & myName & vbCrLf & "Location: " & myLoc, vbInformation, "LINE " & Errline
qd1.Parameters("par1").Value = myName
qd1.Execute
myNewRows = qd1.RecordsAffected
MsgBox myNewRows & " record added", vbInformation, "NEW SITTER"
myNewRows = 0
qd1.Close
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
mySitterID = rs!lastid
qd3.Parameters("par2").Value = myID
qd3.Parameters("par3").Value = mySitterID
qd3.Parameters("par5").Value = myDesc
qd3.Parameters("par6").Value = myLoc
qd3.Execute
myNewRows = qd3.RecordsAffected
MsgBox myNewRows & " record added", vbInformation, "NEW PORTRAIT (year not known)"
myNewRows = 0
qd3.close
Both queries should insert 1 row - the first always does, the second always reports 0 rows inserted (and indeed none is inserted)
I'm painfully aware that this post is much longer than many here but hopefully I've given all the info required.
