I need to run these queries in vb as the "On Open" event for form

[excel/access-chump]

I understand enough vb to have a general idea of what is going on when I look
at vb code but I can't seem to write my own code, despite all the Access
helps.
I'm using Access 2003.
I can get all three queries to work as queries, but when I try to run them
in vb I get a runtime error ‘3211’ and a message: “Data Base engine could not
lock table ‘InvenTracker’ because it is already in use by another person or
process.â€
This is my vb:
DoCmd.OpenQuery "itTable"
DoCmd.OpenQuery "itUpdate"

I have tried putting this in various places: DoCmd.CloseTable, “InvenTrackerâ€

Pass Through Query: InvenTrackerTest = "itTest"
SELECT x.HPKeyword,
x.ReviewerId,
x.addrkey,
x.ChartsScheduled,
x.ChartsRetrieved,
x.ChartsNotRetrieved,
x.AppStart,
x.AppEnd
FROM vw_ScheduledChartByRvwrAppt x
WHERE x.AppStart >= getdate()-3
AND x.AppStart <=getdate()+24
ORDER BY x.AppStart, x.AppEnd

Make-table Query: InvenTrackerMakeTable = "itTable"
SELECT x.*,
x.addrkey & " (" & x.ChartsScheduled & ")" AS addrkey
INTO InvenTracker
FROM itTest AS x;

Update Query: InvenTrackerUpdate = "itUpdate"
UPDATE InvenTracker SET InvenTracker.ReviewerId = Trim([ReviewerId]);

I’ve only been working in Access for about 2 weeks and I’m learning fast but
still rather confused.

Is there a way to run itUpdate as a subquery in itTable?
More questions to follow…

 

[excel/access-chump]

SELECT x.HPKeyword, x.ReviewerId, x.addrkey, x.ChartsScheduled,
x.ChartsRetrieved, x.ChartsNotRetrieved, x.AppStart, x.AppEnd
FROM vw_ScheduledChartByRvwrAppt x
WHERE x.AppStart >= getdate()-3
AND x.AppStart <=getdate()+24
ORDER BY x.AppStart, x.AppEnd

I want to add "AND x.ReviewerId = 'VARIABLE'
I know I can't do this in a query. How would I enter this query with the
variable selected from a combo box?

 

[Robert Morley]

Is there something else that's opening InvenTracker before your queries are
executed (like the Form's Record Source, for example)? That would normally
be the cause of an error like that.

As to your second question, if you want to use the x.* notation, then
there's nothing you can do to combine the two queries. If you want to list
out each and every field, then you can do something like this:

SELECT x.Field1, x.Field2, Trim(x.ReviewerId) AS ReviewerId, x.Field4,
x.addrkey & " (" & x.ChartsScheduled & ")" AS addrkey
INTO InvenTracker
FROM itTest AS x;

If I'm correct in thinking that it's the form's Record Source that's the
problem, and you're trying to create a table for your form to use while the
form is opening, you have two options:

1) Don't specify the table name in the Record Source for the form. After
your queries have run, you can add the lines

Me.RecordSource = "InvenTracker"
Me.Requery

....which will set the record source only AFTER the table has been created.
This has the drawback of making your form a bit harder to design, since it
doesn't actually know what table it's based on. Also, I don't remember if
the Requery line is necessary or not; try omitting it and you'll find out
soon enough!

2) Use an Insert query instead of a Make-Table query, so that rows can be
updated as necessary instead of deleting and re-creating the table every
time. That query is only a slight change to your existing Make-Table query:

INSERT INTO InvenTracker(Field1, Field2, ReviewerId, Field4)
SELECT x.Field1, x.Field2, Trim(x.ReviewerId) AS ReviewerId, x.Field4,
x.addrkey & " (" & x.ChartsScheduled & ")" AS addrkey
FROM itTest AS x;

In the query above, make sure you have all the fields in the same order in
both the INSERT and SELECT portions of the query. Or if you want to use the
..* notation, you would do the following instead:

INSERT INTO InvenTracker
SELECT x.*,
x.addrkey & " (" & x.ChartsScheduled & ")" AS addrkey
FROM itTest AS x;

In this case, you just have to pray that your source and destination columns
always come in the same order.

Finally, using DoCmd.Close acTable, "InvenTracker" command won't help. All
that does is close a table that you've got open on-screen; it does nothing
to close a table that's in use by something else.



Rob

I understand enough vb to have a general idea of what is going on when I look
at vb code but I can't seem to write my own code, despite all the Access
helps.
I'm using Access 2003.
I can get all three queries to work as queries, but when I try to run them
in vb I get a runtime error ‘3211’ and a message: “Data Base engine could not
lock table ‘InvenTracker’ because it is already in use by another person or
process.â€
This is my vb:
DoCmd.OpenQuery "itTable"
DoCmd.OpenQuery "itUpdate"

I have tried putting this in various places: DoCmd.CloseTable, “InvenTrackerâ€

Pass Through Query: InvenTrackerTest = "itTest"
SELECT x.HPKeyword,
x.ReviewerId,
x.addrkey,
x.ChartsScheduled,
x.ChartsRetrieved,
x.ChartsNotRetrieved,
x.AppStart,
x.AppEnd
FROM vw_ScheduledChartByRvwrAppt x
WHERE x.AppStart >= getdate()-3
AND x.AppStart <=getdate()+24
ORDER BY x.AppStart, x.AppEnd

Make-table Query: InvenTrackerMakeTable = "itTable"
SELECT x.*,
x.addrkey & " (" & x.ChartsScheduled & ")" AS addrkey
INTO InvenTracker
FROM itTest AS x;

Update Query: InvenTrackerUpdate = "itUpdate"
UPDATE InvenTracker SET InvenTracker.ReviewerId = Trim([ReviewerId]);

I’ve only been working in Access for about 2 weeks and I’m learning fast but
still rather confused.

Is there a way to run itUpdate as a subquery in itTable?
More questions to follow…

 

[excel/access-chump]

Rob,

Thank you so much for your help! You are fantastic! With your advice I was
able to pare down a bunch of SQL queries. I do have another question now:
I have two sql queries that I need to run inline in VBA because I need the
one to take a value from a combo box. My problem is that I can't find any
help written explicitly enough for me to figure it out. Here is the sql i
need to put in and what I have put in so far.
---------------------------------
Dim strSQL1 As String

strSQL1 = "DELETE InvenTracker.* " _
& "FROM InvenTracker;"
---------------------------------
Dim RevID As String
Dim strSQL2 As String

RevID = [db1].[Form_InventoryTracker].[User_ID]

strSQL2 = "INSERT INTO InvenTracker ( HPKeyword, " _
& "ReviewerId, ReviewerName, addrkey, ChartsScheduled, " _
& "ChartsRetrieved, ChartsNotRetrieved, AppStart, AppEnd, apptkey )" _
& "SELECT x.HPKeyword, Trim(x.ReviewerId) AS ReviewerId, " _
& "x.FirstName & "" "" & x.LastName AS ReviewerName, " _
& "x.addrkey & "" ("" & x.ChartsScheduled & "")""," _
& "x.ChartsScheduled, x.ChartsRetrieved, x.ChartsNotRetrieved, " _
& "DateValue(x.AppStart), DateValue(x.AppEnd), x.apptkey " _
& "FROM dbo_vw_ScheduledChartByRvwrAppt AS x " _
& "WHERE x.AppStart >= Date - 3 " _
& "AND x.AppStart <= Date + 27 " _
& "AND x.ReviewerId = '" & RevID & "' " _
& "ORDER BY x.ReviewerId, x.AppStart, x.AppEnd;"

Debug.Print strSQL2
-------------------------------------
I know that both work fine as plain queries.
(minus the <AND x.ReviewerId = '" & RevID & "'> statement)
I just can't figure out to make them apply to table "InvenTracker"

I am really quit the novice and I am way over my head in this.
All help and advice is GREATLY appreciated!

 

[Robert Morley]

I'm not understanding the problem. Can you explain a little more? Your
code looks okay, at least assuming ReviewerID is a string.



Rob

 

[excel/access-chump]

Rob,
Sorry for the lacking explaination of my problem. I was having trouble
finding an example of inline SQL that showed me the how to run the SQL.
Eventually I figured it out (even though I don't quite understand how it all
works.) I knew that the SQL itself was ok, using the immediate window, but
it took me a little while to realize it was not enough to just have the
syntax written correctly: I actually have to run the SQL! (duh) At any rate,
thank you so much for your help! (AND your patience with my poor
explainations!) ;-)
This is what I have running now. Any suggestions on making it more stable
is very welcome!
________________________
Option Compare Database

Private Sub Form_Open(Cancel As Integer)

Dim db1 As dao.Database
Dim strSQL1 As String
Set db1 = DBEngine(0)(0)

< "DBEngine(0)(0)"- What do the "0"s mean? >

strSQL1 = "DELETE InvenTracker.* " _
& "FROM InvenTracker;"

db1.Execute strSQL1, dbFailOnError

End Sub
-----------------------------
Private Sub User_ID_AfterUpdate()

Dim db1 As dao.Database
Dim RevID As String
Dim strSQL2 As String
Set db1 = DBEngine(0)(0)
RevID = Me.User_ID

< I was having trouble stating the correct path for the "RevID" definition
and finally realized that I could use the "Me." syntax since the object I was
refering to was in the current form. Previously I had it in InvenTrackerSub.
< I know it is garbled here, but the sql runs fine. >

strSQL2 = "INSERT INTO InvenTracker ( HPKeyword, ReviewerId,
ReviewerName, addrkey, ChartsScheduled, ChartsRetrieved, ChartsNotRetrieved,
AppStart, AppEnd, apptkey) " _
& "SELECT x.HPKeyword, Trim(x.ReviewerId) AS ReviewerId,
x.FirstName & "" "" & x.LastName AS ReviewerName, x.addrkey & "" ("" &
x.ChartsScheduled & "")""," _
& "x.ChartsScheduled, x.ChartsRetrieved, x.ChartsNotRetrieved,
DateValue(x.AppStart), DateValue(x.AppEnd), x.apptkey " _
& "FROM dbo_vw_ScheduledChartByRvwrAppt AS x " _
& "WHERE x.AppStart >= Date() - 3 " _
& "AND x.AppStart <= Date() + 27 " _
& "AND x.ReviewerId = '" & RevID & "' " _
& "ORDER BY x.ReviewerId, x.AppStart, x.AppEnd;"

db1.Execute strSQL2, dbFailOnError
----------------------------
'Goes to Form_InvenTrackerSub

On Error GoTo Err_User_ID_AfterUpdate

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "InvenTrackerSub"

stLinkCriteria = "[ReviewerId]=" & "'" & Me![User ID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_User_ID_AfterUpdate:
Exit Sub

Err_User_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_User_ID_AfterUpdate

End Sub
______________________________
--
Thank you for your help!

-The Chump

I'm not understanding the problem. Can you explain a little more? Your
code looks okay, at least assuming ReviewerID is a string.



Rob

Rob,

Thank you so much for your help! You are fantastic! With your advice I was
able to pare down a bunch of SQL queries. I do have another question now:
I have two sql queries that I need to run inline in VBA because I need the
one to take a value from a combo box. My problem is that I can't find any
help written explicitly enough for me to figure it out. Here is the sql i
need to put in and what I have put in so far.
---------------------------------
Dim strSQL1 As String

strSQL1 = "DELETE InvenTracker.* " _
& "FROM InvenTracker;"
---------------------------------
Dim RevID As String
Dim strSQL2 As String

RevID = [db1].[Form_InventoryTracker].[User_ID]

strSQL2 = "INSERT INTO InvenTracker ( HPKeyword, " _
& "ReviewerId, ReviewerName, addrkey, ChartsScheduled, " _
& "ChartsRetrieved, ChartsNotRetrieved, AppStart, AppEnd, apptkey )" _
& "SELECT x.HPKeyword, Trim(x.ReviewerId) AS ReviewerId, " _
& "x.FirstName & "" "" & x.LastName AS ReviewerName, " _
& "x.addrkey & "" ("" & x.ChartsScheduled & "")""," _
& "x.ChartsScheduled, x.ChartsRetrieved, x.ChartsNotRetrieved, " _
& "DateValue(x.AppStart), DateValue(x.AppEnd), x.apptkey " _
& "FROM dbo_vw_ScheduledChartByRvwrAppt AS x " _
& "WHERE x.AppStart >= Date - 3 " _
& "AND x.AppStart <= Date + 27 " _
& "AND x.ReviewerId = '" & RevID & "' " _
& "ORDER BY x.ReviewerId, x.AppStart, x.AppEnd;"

Debug.Print strSQL2
-------------------------------------
I know that both work fine as plain queries.
(minus the <AND x.ReviewerId = '" & RevID & "'> statement)
I just can't figure out to make them apply to table "InvenTracker"

I am really quit the novice and I am way over my head in this.
All help and advice is GREATLY appreciated!

 

[Robert Morley]

Rob,
Sorry for the lacking explaination of my problem. I was having trouble
finding an example of inline SQL that showed me the how to run the SQL.
Eventually I figured it out (even though I don't quite understand how it all
works.) I knew that the SQL itself was ok, using the immediate window, but
it took me a little while to realize it was not enough to just have the
syntax written correctly: I actually have to run the SQL! (duh) At any rate,
thank you so much for your help! (AND your patience with my poor
explainations!) ;-)
This is what I have running now. Any suggestions on making it more stable
is very welcome!

< "DBEngine(0)(0)"- What do the "0"s mean? >

They're short-forms. The longer form might make it more obvious:

DBEngine.Workspaces(0).Databases(0)

In other words, using the database engine, look at the zero-th workspace
(the first one when you're counting from zero) and the zero-th database.
99.99% of the time, that'll be the current database. While it's usually
faster, it's actually not recommended that you do it that way, as it's
possible that that WON'T be the current database in some situations. The
recommended method is to use CurrentDB() in place of DBEngine(0)(0).

Private Sub User_ID_AfterUpdate()

Dim db1 As dao.Database
Dim RevID As String
Dim strSQL2 As String
Set db1 = DBEngine(0)(0)
RevID = Me.User_ID

< I was having trouble stating the correct path for the "RevID" definition
and finally realized that I could use the "Me." syntax since the object I was
refering to was in the current form. Previously I had it in InvenTrackerSub.

Yes, "Me.User_ID" is recommended IF what you're trying to access is the
database field named User_ID. If you're trying to change or access a
control on the form, you should name the control something different than
the field name and use "Me!ctlUser_ID" (assuming you choose to name the
control "ctlUser_ID").

It's also considered a good idea to use ".Value" after all either of those,
though it's not strictly necessary (i.e., "Me!ctlUser_ID.Value"). The
problem is that you're relying on default properties of the control or
field, which can cause problems either a) if the default property changes
from one version to the next (very unlikely) or b) if you're passing the
control or field to another function where it might be interpreted as either
a field/control or as the *contents* of the field/control, in which case you
won't usually get the results you expect.

strSQL2 = "INSERT INTO InvenTracker ( HPKeyword, ReviewerId,
ReviewerName, addrkey, ChartsScheduled, ChartsRetrieved, ChartsNotRetrieved,
AppStart, AppEnd, apptkey) " _
& "SELECT x.HPKeyword, Trim(x.ReviewerId) AS ReviewerId,
x.FirstName & "" "" & x.LastName AS ReviewerName, x.addrkey & "" ("" &
x.ChartsScheduled & "")""," _
& "x.ChartsScheduled, x.ChartsRetrieved, x.ChartsNotRetrieved,
DateValue(x.AppStart), DateValue(x.AppEnd), x.apptkey " _
& "FROM dbo_vw_ScheduledChartByRvwrAppt AS x " _
& "WHERE x.AppStart >= Date() - 3 " _
& "AND x.AppStart <= Date() + 27 " _

The above two lines could be replaced by:

WHERE x.AppStart BETWEEN Date() - 3 AND Date() + 27

Other than that, everything looks good to me.


Rob