On Oct 21, 8:15 am, Stinky Pete <captainm... at (no spam) hotmail.com> wrote:
Hi everyone,
I'm trying to write a past append qry and as this is the first time
I've had a go at it, I think I am way off the mark.
I've got two tables, CRF_log_tbl with a unique (not primary) number
[CRF No] and the second table with 3 fields, code, change and
[required actions]. Yes, I know they have spaces in the field names ;-
( This second table has ordained and pre-defined data. The CRF No is
a text string. The "code" field will change it's number depending on
what type of change is required eg SOPs, controlled forms, equipment
etc. Right now I am just trying to get it working for the "controlled
forms" subset, which begins with 22.
When the associated CRF form is open, upon clicking a specified check
box, I'd like to populate a "tasks" table with the current CRF No and
a subset of the pre-ordained table. What I have so far is below, but
it is hanging right now on the syntax for the WHERE clause. The goal
here is to ensure end users actually complete the task table, cause
they are not doing it "manually" right now despte being trained and
reminded continually. The tasks table concept was desinged so that
the user actually had to think about what they should be entering. Oh
well.
Suggestions would be very much appreciated. This code has been
gleaned from a couple of sources I have found.
Private Sub Controlled_Form_Click()
Dim stLinkCriteria As String 'To get the current CRF No.
Dim sqlstring As String 'SQL statement.
Dim DB As DAO.Database 'Current database
Dim rst
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("CRF_log_tbl", dbOpenDynaset)
stLinkCriteria = "[CRF No]=" & "'" & Me![CRF No] & "'"
If [controlled form] = True Then
sqlstring = "INSERT INTO CRF_tasks_tbl ( [crf no], Code, Change,
[Required Actions] ) " & _
"SELECT stlinkcriteria as [crf no], CRF_main_tasks_list_tbl.Code,
CRF_main_tasks_list_tbl.[Type of Change], CRF_main_tasks_list_tbl.
[Required Actions] " & _
"FROM CRF_main_tasks_list_tbl WHERE ((CRF_main_tasks_list_tbl.Code)
Like "22*"));
End If
End Sub
SP,
When placing SQL inside a string in VBA, I often find that using
single quotes in place of double quotes is required. Perhaps use a
message box to view your SQL or save the string to a file. For
example, I'm not positive that not having a space before SELECT will
work properly, but without seeing what string you're using you might
have no idea what is causing the problem. Writing the string to a
file allows you to paste your SQL into the QBF area for testing. Note
that the expressions outside the string used to place literal values
in the string can contain double quotes without causing a problem.
As a side note, much of the ISO work I'm currently involved with is as
if the auditors and specifications are saying "database. database.
database." They want "closed loops," meaning that all tasks, such as
closing purchase orders, must have a way for a manager to monitor
which ones are still open, similar to a task lisk. All the ways of
monitoring the trends of metrics shout "database." Even the emphasis
on having all corporate procedures properly documented and followed
seems, at least to me, a way of making sure that detailed
specifications will be available for the database programmer
.
James A. Fortune
CDMAPos... at (no spam) FortuneJames.com
