How to reference a Table in a Sub procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rb608 - 28 Jun 2007 22:14 GMT

I'm starting to seriously push my envelope of self-taught
understanding at this point, and I'm stumped at my next step. I've
figured out how passing the variables to the sub procedure works
(whew!); and buoyed by that success, I think as long as I have all of
the same variables I need, why not just put the Recordset operation in
here too.

So, I write up a nice strSelect string ("SELECT * FROM Images WHERE
ImagePrefix = '" & ctlImagePrefix.Text & "....etc. inside the Sub
procedure.

It blows up. "Run time error 424, Object Required."

Aha! I think - I need to put the table "Images" into the variable
list on both ends. so I add ", objTable as Object" to the end of the
Sub variables and add the table name "Images" to the Call variables.
I also rewrite the string to be
"SELECT * FROM " & objTable & "WHERE....."

No good. Now I get "Compile Error: ByRef argument type mismatch."

Clearly I'm over my head here. I've never gotten this deep into the
bowels of Access. I could do trial-and-error for days and never
figure this out. Needless to say, the Help file isn't much help.
What's the correct syntax/procedure for what I'm trying to do?

Tx again.

Reply to this Message

Douglas J. Steele - 28 Jun 2007 22:20 GMT

You'll have to show us the rest of your code. Assigning a SQL statement to a
string, by itself, cannot possibly generate an 424 error.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> I'm starting to seriously push my envelope of self-taught
> understanding at this point, and I'm stumped at my next step. I've
[quoted text clipped - 23 lines]
>
> Tx again.

Reply to this Message

rb608 - 29 Jun 2007 21:19 GMT

"Douglas J. Steele" wrote in message

> You'll have to show us the rest of your code. Assigning a SQL statement to
> a string, by itself, cannot possibly generate an 424 error.

Okey dokey; here are the two procedures, three ways. First, these are the
procedures that worked fine:

Sub ImageFile(ctlImagePath As Control, ctlSubfolder As Control,
ctlImagePrefix As Control, _
iSpace As Integer, iDigits As Integer, ctlSequence As Control,
ctlFileExt As Control, _
strWholeName As String, strImageFile As String)
Dim strDigits As String
strWholeName = ctlImagePath
strWholeName = strWholeName & ctlImagePrefix
If iSpace = 1 Then
strWholeName = strWholeName & " "
strImageFile = ctlImagePrefix & " "
Else
strImageFile = ctlImagePrefix
End If
ctlSequence.SetFocus
Select Case iDigits - Len(ctlSequence.Text)
Case 0
strDigits = ctlSequence.Text
Case 1
strDigits = "0" & ctlSequence.Text
Case 2
strDigits = "00" & ctlSequence.Text
Case 3
strDigits = "000" & ctlSequence.Text
Case 4
strDigits = "0000" & ctlSequence.Text
End Select
strWholeName = strWholeName & strDigits & ctlFileExt
strImageFile = strImageFile & strDigits & ctlFileExt
End Sub

Private Sub cmdTest_Click()
Dim strFileName As String, strImageFile As String
Call ImageFile(Me!txtImagePath, Me!txtSubfolder, Me!txtImagePrefix, _
Me!txtSpace, Me!txtDigits, Me!txtSequence, Me!txtFileExt, strFileName,
strImageFile)
Me!txtFullPath = strFileName
Me!ImgFrame1.Picture = strFileName
Me!txtImageFile = strImageFile
Me!txtCaption.SetFocus
End Sub

Then I tried to add a recordset operation thusly. This gave me the 424
error:

Sub ImageFile(ctlImagePath As Control, ctlSubfolder As Control,
ctlImagePrefix As Control, _
iSpace As Integer, iDigits As Integer, ctlSequence As Control,
ctlFileExt As Control, _
strWholeName As String, strImageFile As String)
Dim strDigits As String
strWholeName = ctlImagePath
strWholeName = strWholeName & ctlImagePrefix
If iSpace = 1 Then
strWholeName = strWholeName & " "
strImageFile = ctlImagePrefix & " "
Else
strImageFile = ctlImagePrefix
End If
ctlSequence.SetFocus
Select Case iDigits - Len(ctlSequence.Text)
Case 0
strDigits = ctlSequence.Text
Case 1
strDigits = "0" & ctlSequence.Text
Case 2
strDigits = "00" & ctlSequence.Text
Case 3
strDigits = "000" & ctlSequence.Text
Case 4
strDigits = "0000" & ctlSequence.Text
End Select
strWholeName = strWholeName & strDigits & ctlFileExt
strImageFile = strImageFile & strDigits & ctlFileExt
Dim rstCaption As DAO.Recordset, dbs As DAO.Database
Dim strSelect As String
strSelect = "SELECT * FROM Images WHERE ProjectNo = '" & ctlProjectNo.Text &
"' AND ImagePrefix = '" & ctlImagePrefix.Text _
& "' AND Subfolder = '" & ctlSubfolder.Text & "' AND Sequence = " &
ctlSequence.Text & "' AND FileExt = '" _
& ctlFileExt & "'"
Set dbs = CurrentDb
Set rstCaption = dbs.OpenRecordset(strCaption)
End Sub

Private Sub cmdTest_Click()
Dim strFileName As String, strImageFile As String
Call ImageFile(Me!txtImagePath, Me!txtSubfolder, Me!txtImagePrefix, _
Me!txtSpace, Me!txtDigits, Me!txtSequence, Me!txtFileExt, strFileName,
strImageFile)
Me!txtFullPath = strFileName
Me!ImgFrame1.Picture = strFileName
Me!txtImageFile = strImageFile
Me!txtCaption.SetFocus
End Sub

Then I tried adding the Table as a variable. This gave me the ByRef error:

Sub ImageFile(ctlImagePath As Control, ctlSubfolder As Control,
ctlImagePrefix As Control, _
iSpace As Integer, iDigits As Integer, ctlSequence As Control,
ctlFileExt As Control, _
strWholeName As String, strImageFile As String, objTable as Object)
Dim strDigits As String
strWholeName = ctlImagePath
strWholeName = strWholeName & ctlImagePrefix
If iSpace = 1 Then
strWholeName = strWholeName & " "
strImageFile = ctlImagePrefix & " "
Else
strImageFile = ctlImagePrefix
End If
ctlSequence.SetFocus
Select Case iDigits - Len(ctlSequence.Text)
Case 0
strDigits = ctlSequence.Text
Case 1
strDigits = "0" & ctlSequence.Text
Case 2
strDigits = "00" & ctlSequence.Text
Case 3
strDigits = "000" & ctlSequence.Text
Case 4
strDigits = "0000" & ctlSequence.Text
End Select
strWholeName = strWholeName & strDigits & ctlFileExt
strImageFile = strImageFile & strDigits & ctlFileExt
Dim rstCaption As DAO.Recordset, dbs As DAO.Database
Dim strSelect As String
strSelect = "SELECT * FROM " & objTable & " WHERE ProjectNo = '" &
ctlProjectNo.Text & "' AND ImagePrefix = '" & ctlImagePrefix.Text _
& "' AND Subfolder = '" & ctlSubfolder.Text & "' AND Sequence = " &
ctlSequence.Text & "' AND FileExt = '" _
& ctlFileExt & "'"
Set dbs = CurrentDb
Set rstCaption = dbs.OpenRecordset(strCaption)
End Sub

Private Sub cmdTest_Click()
Dim strFileName As String, strImageFile As String
Call ImageFile(Me!txtImagePath, Me!txtSubfolder, Me!txtImagePrefix, _
Me!txtSpace, Me!txtDigits, Me!txtSequence, Me!txtFileExt, strFileName,
strImageFile, Images)
Me!txtFullPath = strFileName
Me!ImgFrame1.Picture = strFileName
Me!txtImageFile = strImageFile
Me!txtCaption.SetFocus
End Sub

Thanks for looking at this.
Joe

Reply to this Message

Douglas J. Steele - 29 Jun 2007 22:01 GMT

George is correct that you shouldn't be using the Text property of the
controls.

Your code won't work anyhow, though. You're setting strSelect in code, but
you're using strCaption in opening the recordset. Besides, all you're doing
is opening a recordset, then closing the sub. Since the scope of rstCaption
is that sub, ending the sub causes the recordset to be destroyed before
you've done anything with it.

In method 3, if Images is supposed to be the name of a table, you can't pass
it that way. You might be able to use CurrentDb().TableDefs("Image"), but
I'm not sure that's necessarily going to work either.

"SELECT * FROM Images WHERE" would be all you needed.

What, in words, are you trying to do?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> "Douglas J. Steele" wrote in message
>> You'll have to show us the rest of your code. Assigning a SQL statement
[quoted text clipped - 155 lines]
> Thanks for looking at this.
> Joe

Reply to this Message

rb608 - 30 Jun 2007 01:21 GMT

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message

> What, in words, are you trying to do?

The primary purpose of the database (and admittedly this is "recreational"
coding) was born out of one part of my job. I frequently make inspections
of buildings for due diligence or pre-construction structural surveys. As
part of these inspections, I regularly take a few dozen pictures that I then
caption and include in a report. Putting these into a MS Word table,
however, is cumbersome, and it occurred to me that I could easily (ha ha)
write an Access application wherby I could browse the images on a form and
add captions there. This basic functionality I managed simply enough; but,
as is my wont, I keep looking for ways to make the thing work better,
easier, smoother, more idiot-proof (a tall order given that an idiot is
writing the code <g>).

So where I am with the code in question is this. I have tables (Images,
ProjectData) in which the fields stored include the project number, project
name, subfolder, image prefix, sequence number, and type of image (.jpg
etc.). To display a specific image on a form, I need to concatenate these
fields into a single file path and assign that string to the image frame's
picture property (e.g. imgFrame1.Picture =strFileName).

The complications lie in the fact that depending on who took the images and
how they copied them to the drive, the sequence numbers may be 1, 2, 3, or
more digits (hence need for that digits Select Case exercise.) Also, some
image file names have a space between the prefix and the sequence number and
some do not (which is why there's that frSpace thing). In the end, a
concatenated file name may be something like:
P:\2007\07023500\Design\Photos\Kirkley 034.jpg.

In browsing through the images, if I use "Next" or "Previous" command
buttons, I need to increment the sequence number and rebuild the file name
every time I skip ahead or back. Also, if I go to the first (or last)
record, or go to a specified sequence number, I do the concatenation again.
Now, it's not a terrible problem to just write this code into the procedure
for each command button; but I thought I'd take a shot at elegance and try
writing a Sub procedure I could just call from either procedure.

All of that was fine until I got the idea to use the Sub procedure for the
recordset procedure to help identify the next, last, first, or whatever
record in the subfolder. That's what I was starting to do when I posted my
question.

Tx,
Joe

Reply to this Message

George Nicholson - 28 Jun 2007 22:57 GMT

> ctlImagePrefix.Text

Assuming ctlImagePrefix is a Textbox or Combobox, try using
ctlImagePrefix.Value

The Text property is be tricky to use because it requires that the specified
control have the focus. The Value property has no such requirement. This
*may* be why your code wasn't working, but I would have expected a different
error message

2)
If you have a table named Images in the current db then "SELECT * FROM
Images.." is fine.

However, *if* you wanted to do something like this....

> "SELECT * FROM " & objTable & "WHERE....."

You declared objTable as an object but the select statement wants a text
string (the table name):

Here is one approach to doing the same thing correctly (also note the
additional space before the W):

Dim tdf as DAO.TableDef
Set tdf = CurrentDB.TableDefs("Images")

"SELECT * FROM " & tdf.Name & " WHERE....."

3) For easier debugging, make sure you assign your sql string to a variable:
strSQL = "SELECT * FROM Images WHERE ...."
then, place a breakpoint in your code on the line that will execute your
completed strSQL. When the breakpoint is reached type the following in the
Immediate window (View>Immediate):
?strSQL
What displays will be the string that Access is about to try to execute.
Review space/quote placements, variable/control values, etc., and edit your
code as necessary. Quite often what is wrong is very obvious.

If you are still having problems, show us the entire line that "blows up".
If its something like:

Set rs = CurrentDB.OpenRecordset(strSQL)

Then "Object Required" might not refer to a problem with strSQL, it would
probably refer to the incorrect use of CurrentDB....

HTH,

> I'm starting to seriously push my envelope of self-taught
> understanding at this point, and I'm stumped at my next step. I've
[quoted text clipped - 23 lines]
>
> Tx again.

Reply to this Message