TK421
asked on
VB / SQL Recordset Validation
Purpose of code: See if an existing record is completed before ANOTHER record can be created for the same "Log Number".
Here is what I am trying to do:
1) Open a recordset from a stored query.
2) If recordset finds a record, continue. If recordset is empty, display msgbox. QUIT.
3) If the SQL returns data, it is the record I need at the top (it's already sorted). If a field [Date Poscripted] in that record is not Null, continue, else display msgbox "Existing record not completed..."
4) If record does exist AND is completed, then copy some fields to the new record.
That's it. I imagine this general process can not be too uncommon. Am I approaching this properly? Is there a better way? Perhaps SQL embedded in the code (no need for a stored query?) OR is there a way to copy all the fields from the old record to the new record, THEN 'Null' the fields I need to? This would eliminate the need for all fields to be copied to be listed in the code itself.
The code(s) below partially work and are a basic outline. At present, this line:
Set rs = qdf.OpenRecordset(dbOpenDy
produces this error:
"Too few parameters. Expected 2."
Your thoughts - or what can I do to get this existing code (see below) working? Mega thanks!
Stored Query (SQL):
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log
Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
The VB Code under a command button:
Private Sub Revise_Click()
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
'Settings Here
Set db = CurrentDb
Set qdf = db.QueryDefs("qryArtNumber
qdf.Parameters("[Enter the Art Log Number]") = InputBox("Enter the Art Log Number", , "Value Required")
'-------------------------
Set rs = qdf.OpenRecordset(dbOpenDy
DoCmd.Close acForm, "Popup1"
'Validation here
If rs.RecordCount > 0 Then
GoTo Cont1:
Else
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
GoTo AddNewFields
End If
Cont1:
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
If (Nz(varIsApproved, "") <> "") Then 'If Writer Approval date is not equal to null then
DoCmd.OpenForm "Form1"
Set rs2 = Forms![Form1].RecordsetClo
'open Form and copy some fields from original recordset to the new record
rs2.AddNew
rs2![Manual] = rs![Manual]
rs2![Chapter] = rs![Chapter]
rs2![Section] = rs![Section]
'there are many other fields to go here
Else
MsgBox "This Art Log Number is currently being worked. Please see the bla bla Department for assistance.", vbInformation, "Title Here"
DoCmd.OpenForm "Form1", acNormal, , , acFormAdd
End If
AddNewFields:
[Forms]![Form1]![Author] = MyCurrentUser()
[Forms]![Form1]![Extension
[Forms]![Form1]![Art Number] = "0"
DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
End1:
End Sub
Here is what I am trying to do:
1) Open a recordset from a stored query.
2) If recordset finds a record, continue. If recordset is empty, display msgbox. QUIT.
3) If the SQL returns data, it is the record I need at the top (it's already sorted). If a field [Date Poscripted] in that record is not Null, continue, else display msgbox "Existing record not completed..."
4) If record does exist AND is completed, then copy some fields to the new record.
That's it. I imagine this general process can not be too uncommon. Am I approaching this properly? Is there a better way? Perhaps SQL embedded in the code (no need for a stored query?) OR is there a way to copy all the fields from the old record to the new record, THEN 'Null' the fields I need to? This would eliminate the need for all fields to be copied to be listed in the code itself.
The code(s) below partially work and are a basic outline. At present, this line:
Set rs = qdf.OpenRecordset(dbOpenDy
produces this error:
"Too few parameters. Expected 2."
Your thoughts - or what can I do to get this existing code (see below) working? Mega thanks!
Stored Query (SQL):
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log
Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
The VB Code under a command button:
Private Sub Revise_Click()
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
'Settings Here
Set db = CurrentDb
Set qdf = db.QueryDefs("qryArtNumber
qdf.Parameters("[Enter the Art Log Number]") = InputBox("Enter the Art Log Number", , "Value Required")
'-------------------------
Set rs = qdf.OpenRecordset(dbOpenDy
DoCmd.Close acForm, "Popup1"
'Validation here
If rs.RecordCount > 0 Then
GoTo Cont1:
Else
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
GoTo AddNewFields
End If
Cont1:
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
If (Nz(varIsApproved, "") <> "") Then 'If Writer Approval date is not equal to null then
DoCmd.OpenForm "Form1"
Set rs2 = Forms![Form1].RecordsetClo
'open Form and copy some fields from original recordset to the new record
rs2.AddNew
rs2![Manual] = rs![Manual]
rs2![Chapter] = rs![Chapter]
rs2![Section] = rs![Section]
'there are many other fields to go here
Else
MsgBox "This Art Log Number is currently being worked. Please see the bla bla Department for assistance.", vbInformation, "Title Here"
DoCmd.OpenForm "Form1", acNormal, , , acFormAdd
End If
AddNewFields:
[Forms]![Form1]![Author] = MyCurrentUser()
[Forms]![Form1]![Extension
[Forms]![Form1]![Art Number] = "0"
DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
End1:
End Sub
ASKER
A97 and I'm new to DAO. YIKES!
instead of rs.recordcount i would use rs.EOF instead. recordcount doens't always produce the desired result. sometimes it'll give you 1 even if there are more than one record in the recordset. just an FYI i had to figure out on my own! :)
in your validation you use an if statement and if the condition is true you use a goto statement. that goto statement is unnecessary if the if statement evaluates to true...
when using the nz() method, you don't need to include the zero-length string such as : nz(NullVariable, ""). instead, the "" is implied. this will reduce your typing (albeit only by a little) to give you : nz(NullVariable) which still returns "".
other than the ADO vs DAO issue, and the few comments i noted above it seems ok.
maybe another expert can find something i didn't or comment on MY comments... ;)
good luck
dovholuk
in your validation you use an if statement and if the condition is true you use a goto statement. that goto statement is unnecessary if the if statement evaluates to true...
when using the nz() method, you don't need to include the zero-length string such as : nz(NullVariable, ""). instead, the "" is implied. this will reduce your typing (albeit only by a little) to give you : nz(NullVariable) which still returns "".
other than the ADO vs DAO issue, and the few comments i noted above it seems ok.
maybe another expert can find something i didn't or comment on MY comments... ;)
good luck
dovholuk
if you're using a97 you'll be all set with the reference.
instead of using:
Set rs = qdf.OpenRecordset(dbOpenDy
try just using:
Set rs = qdf.OpenRecordset()
see if that produces an error?
i tried your code with my own parameter query and it worked fine...
can you remove the parameter from your query and run it from the query editor? does it work?
dovholuk
instead of using:
Set rs = qdf.OpenRecordset(dbOpenDy
try just using:
Set rs = qdf.OpenRecordset()
see if that produces an error?
i tried your code with my own parameter query and it worked fine...
can you remove the parameter from your query and run it from the query editor? does it work?
dovholuk
Why not try to see what parameters the program thinks it needs. After
Set qdf = db.QueryDefs("qryArtNumber
insert the following code
dim prm as parameter
For Each prm in qdf.parameters
debug.print prm.name
next prm
and then check the debug window for the results.
Hopefully this will help you to identify what is going wrong.
Cheers
Set qdf = db.QueryDefs("qryArtNumber
insert the following code
dim prm as parameter
For Each prm in qdf.parameters
debug.print prm.name
next prm
and then check the debug window for the results.
Hopefully this will help you to identify what is going wrong.
Cheers
Open the query qryArtNumber and see if the parameter are in the same line.
I have the same error when i paste your Query string
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log
Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
But if change it and put the name of the parameter in the same line there is not problem
Make sure the every name in brakets are in the same line
Don't put :
[Enter the Art Log
Number]
Put:
[Enter the Art Log Number]
When i try with this change the error dissapear
I have the same error when i paste your Query string
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log
Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
But if change it and put the name of the parameter in the same line there is not problem
Make sure the every name in brakets are in the same line
Don't put :
[Enter the Art Log
Number]
Put:
[Enter the Art Log Number]
When i try with this change the error dissapear
ASKER
Okay, I am past the "Too few parameters. Expected 2." error. Here's what I have done so far:
1) dovholuk - I modified the SQL statement (the parameters on one line)
2) dovholuk - Changed this line: Set rs = qdf.OpenRecordset(dbOpenDy
to this: Set rs = qdf.OpenRecordset()
Either of these lines produce this error: "Data type mismatch in criteria."
3) xtrahands - I added your error checking code and get this:
"prm.Name = <Objectvariable or With block variable not defined."
prm = nothing
Is something wrong with the SQL in the stored query?
SQL:
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
Thanks for all the input. At the conclusion of this Q, I'll divy up the pts to all that have helped. :)
TK
1) dovholuk - I modified the SQL statement (the parameters on one line)
2) dovholuk - Changed this line: Set rs = qdf.OpenRecordset(dbOpenDy
to this: Set rs = qdf.OpenRecordset()
Either of these lines produce this error: "Data type mismatch in criteria."
3) xtrahands - I added your error checking code and get this:
"prm.Name = <Objectvariable or With block variable not defined."
prm = nothing
Is something wrong with the SQL in the stored query?
SQL:
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
Thanks for all the input. At the conclusion of this Q, I'll divy up the pts to all that have helped. :)
TK
can you fire it off (stripped of any proprietary data) to dovholuk@yahoo.com? or post it on the net somewhere?
if so, i can give it the once over and see if i can help you out...
i can't see what's wrong with your code from here... like i said, i tried it with my own parameter query and it worked like a champ.
try just running the query on its own. see if that works.
also, try this code below and tell me if it works. NOTE: when prompted, enter VALID input.
'******************** BEGIN COPY ************************
Function TestTK421()
Dim rs As DAO.Recordset
Dim LogNumber As String
LogNumber = InputBox("Enter the Art Log Number", "Enter Art Log Number")
If Left(LogNumber, 1) = "a" Then LogNumber = Mid(LogNumber, 2)
Set rs = CurrentDb.OpenRecordset("S
& "FROM [Main Table] " _
& "WHERE [Art Number] = '" & LogNumber & "' " _
& "ORDER BY [Date Submitted To Art Dept] DESC;")
If rs.EOF Then
MsgBox "THE QUERY RETURNS NOTHING"
Else
MsgBox "THE TEST HAS WORKED, DATA RETURNED"
End If
rs.Close
Set rs = Nothing
End Function
'******************** END COPY ************************
try running this function and see if it works. it should.
dovholuk
if so, i can give it the once over and see if i can help you out...
i can't see what's wrong with your code from here... like i said, i tried it with my own parameter query and it worked like a champ.
try just running the query on its own. see if that works.
also, try this code below and tell me if it works. NOTE: when prompted, enter VALID input.
'******************** BEGIN COPY ************************
Function TestTK421()
Dim rs As DAO.Recordset
Dim LogNumber As String
LogNumber = InputBox("Enter the Art Log Number", "Enter Art Log Number")
If Left(LogNumber, 1) = "a" Then LogNumber = Mid(LogNumber, 2)
Set rs = CurrentDb.OpenRecordset("S
& "FROM [Main Table] " _
& "WHERE [Art Number] = '" & LogNumber & "' " _
& "ORDER BY [Date Submitted To Art Dept] DESC;")
If rs.EOF Then
MsgBox "THE QUERY RETURNS NOTHING"
Else
MsgBox "THE TEST HAS WORKED, DATA RETURNED"
End If
rs.Close
Set rs = Nothing
End Function
'******************** END COPY ************************
try running this function and see if it works. it should.
dovholuk
Make sure that every parameter in the stored query are in the same line
"TK421, why aren't you at your post?"
dovholuk
dovholuk
ASKER
To all who have replied:
As you may be aware, this site has been acting up. The last few times I tried to access this site, it was unavailable. Please let me review these comments and I will reply...I hope...
TK
As you may be aware, this site has been acting up. The last few times I tried to access this site, it was unavailable. Please let me review these comments and I will reply...I hope...
TK
ASKER
dovholuk,
I tried your test function from above. I placed the code under a command button on a form. I receive the same errors as my stored query:
"Data type mismatch in criteria."
Here's the SQL in the stored query:
==========================
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*, [Main Table].[Date Submitted To Art Dept], [Main Table].[Art Number]
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
==========================
Again, all of the params above are on individual lines. When I attempt to run the code using the command button, I get this error: "Data type mismatch in criteria."
When I run the query by itself, I get this error:
"This expression is typed incorrectly, or it is too complex to be evaluated..."
NOW, if I remove this "PARAMETERS [Enter the Art Log Number] Text;"
the query (when run by itself) works perfectly.
When I add PARAMETERS [Enter the Art Log Number] Text;" to the stored query, I get this result:
"Data type mismatch in criteria."
I have vertually no experience in SQL (as you can tell). I'm not sure where to begin. In short review, I'd like to do the following:
1) Press command button
2) Enter File number and query the database to see if it exists.
3) Upon it existing, validate it is a 'closed' record.
4) If closed, copy certain fields from the old record to a new one.
Due to the content and structure, I really can not post the database or a portion of it without serious work. I hope I can avoid that! Where do I begin to dubug this process? HELP!
Uppin' the pts on this motha...
TK
Do I have the necessary items declared before the query is called?
I tried your test function from above. I placed the code under a command button on a form. I receive the same errors as my stored query:
"Data type mismatch in criteria."
Here's the SQL in the stored query:
==========================
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*, [Main Table].[Date Submitted To Art Dept], [Main Table].[Art Number]
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
==========================
Again, all of the params above are on individual lines. When I attempt to run the code using the command button, I get this error: "Data type mismatch in criteria."
When I run the query by itself, I get this error:
"This expression is typed incorrectly, or it is too complex to be evaluated..."
NOW, if I remove this "PARAMETERS [Enter the Art Log Number] Text;"
the query (when run by itself) works perfectly.
When I add PARAMETERS [Enter the Art Log Number] Text;" to the stored query, I get this result:
"Data type mismatch in criteria."
I have vertually no experience in SQL (as you can tell). I'm not sure where to begin. In short review, I'd like to do the following:
1) Press command button
2) Enter File number and query the database to see if it exists.
3) Upon it existing, validate it is a 'closed' record.
4) If closed, copy certain fields from the old record to a new one.
Due to the content and structure, I really can not post the database or a portion of it without serious work. I hope I can avoid that! Where do I begin to dubug this process? HELP!
Uppin' the pts on this motha...
TK
Do I have the necessary items declared before the query is called?
you've provided very good information. the query WILL run without the parameters line. that's VERY important. now what i'd tell you is to change the [Enter the Art Log Number] to something shorter, like [LogNum]. replacing all instances of course. such as:
==========================
PARAMETERS [LogNum] Text;
SELECT TOP 1 [Main Table].*, [Main Table].[Date Submitted To Art Dept], [Main Table].[Art Number]
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([LogNum]
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
==========================
try that and see how things go... i'll check back here regularly as e-mail notif's aren't working.
can i provide you a tip? ok, thanks... i am confident that if you used a better "naming convention" that you'd find your sql comprehension to increase. it'd also make things easier to read. for instance. you have a table named "Main Table". instead of using a space (which TOTALLY FUBARs much of your programming/sql), simply take the space out. giving you MainTable. Same goes for all your fields. "Date Submitted To Art Dept" is wonderful for it's description value, but DateSubmitted works just as well, don't you think?
that's just my take on things. the sql you have above, if you had a table like:
PROPOSED TABLE
=======================
MainTable
-----------------------
DateSubmitted
ArtNumber
your sql could be simplified as such:
==========================
PARAMETERS [LogNum] Text;
SELECT TOP 1 *, DateSubmitted, ArtNumber
FROM MainTable
WHERE ArtNumber = IIf(Left([LogNum],1)="a",M
ORDER BY DateSubmitted DESC;
==========================
that's a little easier to read. please note though, that much of issue with readibility comes from Access itself. access puts in all those ['s and ]'s and refers explicitly to the tables EVERYWHERE (note i took out almost EVERY [Main Table] that acces put in)
just some extra info for ya! :)
hope it helps. try shortening the parameter name and get back.
-dovholuk
==========================
PARAMETERS [LogNum] Text;
SELECT TOP 1 [Main Table].*, [Main Table].[Date Submitted To Art Dept], [Main Table].[Art Number]
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([LogNum]
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
==========================
try that and see how things go... i'll check back here regularly as e-mail notif's aren't working.
can i provide you a tip? ok, thanks... i am confident that if you used a better "naming convention" that you'd find your sql comprehension to increase. it'd also make things easier to read. for instance. you have a table named "Main Table". instead of using a space (which TOTALLY FUBARs much of your programming/sql), simply take the space out. giving you MainTable. Same goes for all your fields. "Date Submitted To Art Dept" is wonderful for it's description value, but DateSubmitted works just as well, don't you think?
that's just my take on things. the sql you have above, if you had a table like:
PROPOSED TABLE
=======================
MainTable
-----------------------
DateSubmitted
ArtNumber
your sql could be simplified as such:
==========================
PARAMETERS [LogNum] Text;
SELECT TOP 1 *, DateSubmitted, ArtNumber
FROM MainTable
WHERE ArtNumber = IIf(Left([LogNum],1)="a",M
ORDER BY DateSubmitted DESC;
==========================
that's a little easier to read. please note though, that much of issue with readibility comes from Access itself. access puts in all those ['s and ]'s and refers explicitly to the tables EVERYWHERE (note i took out almost EVERY [Main Table] that acces put in)
just some extra info for ya! :)
hope it helps. try shortening the parameter name and get back.
-dovholuk
any progress TK421?
dovholuk
dovholuk
ASKER
dovholuk,
I tried to reply here a few times, but the site was acting up. And thanks for the info about the emails not working, I did not know. I will check here VERY frequently because I'd like to close the Q and not drag it out. I'm sure YOU would like that as well!
1) After changing (see code below) [Date Submitted To Art Number] to [ArtNum], I still receive this error "This expression is typed incorrectly or is too complex to be evaluated..."
2) Naming Conventions - When I created this DB, I had no idea of naming conventions. Oh boy, how I have learned THAT mistake! If/when I decide to rename fields and VB code, is that all I have to change?
A) field names in tables
B) queries
C) VB code?
Is that it?
I don't know where to go from here. Do you think the problem is my (lack of) naming conventions. Should I fix the naming before trying anything else, or is my SQL still messed up? Your opinion?
Here's the latest SQL in the stored query:
PARAMETERS [LogNum] Text;
SELECT TOP 1 *, [Date Submitted To Art Dept], [Art Number]
FROM [Main Table]
WHERE ((([Art Number])=IIf(Left([LogNum]
ORDER BY [Date Submitted To Art Dept] DESC;
TK
I tried to reply here a few times, but the site was acting up. And thanks for the info about the emails not working, I did not know. I will check here VERY frequently because I'd like to close the Q and not drag it out. I'm sure YOU would like that as well!
1) After changing (see code below) [Date Submitted To Art Number] to [ArtNum], I still receive this error "This expression is typed incorrectly or is too complex to be evaluated..."
2) Naming Conventions - When I created this DB, I had no idea of naming conventions. Oh boy, how I have learned THAT mistake! If/when I decide to rename fields and VB code, is that all I have to change?
A) field names in tables
B) queries
C) VB code?
Is that it?
I don't know where to go from here. Do you think the problem is my (lack of) naming conventions. Should I fix the naming before trying anything else, or is my SQL still messed up? Your opinion?
Here's the latest SQL in the stored query:
PARAMETERS [LogNum] Text;
SELECT TOP 1 *, [Date Submitted To Art Dept], [Art Number]
FROM [Main Table]
WHERE ((([Art Number])=IIf(Left([LogNum]
ORDER BY [Date Submitted To Art Dept] DESC;
TK
i've recieved the "query too complex" error when joining tables on tables, but i don't ever use parameter queries. instead, i code all my queries. it makes it easier for me to change things in the future, but that's just me.
that being said, how about you try this instead. in your original post, you had an inputbox getting the "art log number". that's how i'd do it too (if i didn't get the info from a form).
let's take your original code and modify it, ever so slightly as follows. change the following:
==========================
Set db = CurrentDb
Set qdf = db.QueryDefs("qryArtNumber
qdf.Parameters("[Enter the Art Log Number]") = InputBox("Enter the Art Log Number", , "Value Required")
'-------------------------
Set rs = qdf.OpenRecordset(dbOpenDy
==========================
to
==========================
dim ArtLogNumber as string
Set db = CurrentDb
set qdf = db.createquerydef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
if artlognumber = "" then
msgbox "A value is required", vbcritical, "No Value Entered"
exit sub
else
'-------------------------
qdf.sql = "SELECT * FROM qryArtNumber WHERE [Art Number] = '" & artlognumber & "'"
Set rs = qdf.OpenRecordset() '<----- PRODUCES ERROR
end if
==========================
i am pretty sure that my syntax is right, if not i'll try to fix it. try that out and see if it works....
dovholuk
that being said, how about you try this instead. in your original post, you had an inputbox getting the "art log number". that's how i'd do it too (if i didn't get the info from a form).
let's take your original code and modify it, ever so slightly as follows. change the following:
==========================
Set db = CurrentDb
Set qdf = db.QueryDefs("qryArtNumber
qdf.Parameters("[Enter the Art Log Number]") = InputBox("Enter the Art Log Number", , "Value Required")
'-------------------------
Set rs = qdf.OpenRecordset(dbOpenDy
==========================
to
==========================
dim ArtLogNumber as string
Set db = CurrentDb
set qdf = db.createquerydef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
if artlognumber = "" then
msgbox "A value is required", vbcritical, "No Value Entered"
exit sub
else
'-------------------------
qdf.sql = "SELECT * FROM qryArtNumber WHERE [Art Number] = '" & artlognumber & "'"
Set rs = qdf.OpenRecordset() '<----- PRODUCES ERROR
end if
==========================
i am pretty sure that my syntax is right, if not i'll try to fix it. try that out and see if it works....
dovholuk
ugh, i forgot to mention that you should take the parameters out of your original (qryArtNumber) query.
using the method above, you won't need a parameter.
dovholuk
using the method above, you won't need a parameter.
dovholuk
ASKER
I did the changes you posted above. Here' s the latest (portion) of the code:
==========================
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Dim ArtLogNumber As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
qdf.SQL = "SELECT * FROM qryArtNumber WHERE [Art Number] = '" & ArtLogNumber & "'"
Set rs = qdf.OpenRecordset() '<----- STILL PRODUCES ERROR
End If
==========================
Set rs = qdf.OpenRecordset() now returns this error: "To few parameters. Expected 1."
This error is with the PARAMETERS and without the PARAMETERS in the stored query. Ah! Have I missed any declarations?
You mention "instead, I code all my queries. it makes it easier for me to change things in the future, but that's just me." I'd love to know how to code the queries as well. Once my current issue is resolved, I'd like to know how to do just that.
==========================
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Dim ArtLogNumber As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
qdf.SQL = "SELECT * FROM qryArtNumber WHERE [Art Number] = '" & ArtLogNumber & "'"
Set rs = qdf.OpenRecordset() '<----- STILL PRODUCES ERROR
End If
==========================
Set rs = qdf.OpenRecordset() now returns this error: "To few parameters. Expected 1."
This error is with the PARAMETERS and without the PARAMETERS in the stored query. Ah! Have I missed any declarations?
You mention "instead, I code all my queries. it makes it easier for me to change things in the future, but that's just me." I'd love to know how to code the queries as well. Once my current issue is resolved, I'd like to know how to do just that.
ok let's ditch all this and just "code the query" as i was talking about before...
here's how i'd do it:
'=========================
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Dim ArtLogNumber As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Function
Else
'art log number was entered.
ArtLogNumber = IIf(Left(ArtLogNumber, 1) = "a", Mid(ArtLogNumber, 2), ArtLogNumber)
qdf.sql = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = '" & ArtLogNumber & "' " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;"
Set rs = qdf.OpenRecordset() '<----- SHOULD NOT PRODUCE ERROR :-)
End If
'=========================
if this doesn't work... so help me! ;)
let me know...
dovholuk
here's how i'd do it:
'=========================
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Dim ArtLogNumber As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Function
Else
'art log number was entered.
ArtLogNumber = IIf(Left(ArtLogNumber, 1) = "a", Mid(ArtLogNumber, 2), ArtLogNumber)
qdf.sql = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = '" & ArtLogNumber & "' " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;"
Set rs = qdf.OpenRecordset() '<----- SHOULD NOT PRODUCE ERROR :-)
End If
'=========================
if this doesn't work... so help me! ;)
let me know...
dovholuk
notice that i didn't refer to ANY stored queries. while stored queries are a bit faster (as they can be optimized by access when saved) i've never noticed a significant difference.
plus, as i stated, i think it's easier to edit this than a stored query... but that's just me...
dovholuk
plus, as i stated, i think it's easier to edit this than a stored query... but that's just me...
dovholuk
ASKER
Cool. I'm off to lunch. I'll try and reply after...thanks!
ASKER
I was too curious and tried it. My nemesis returns:
"Data type mismatch in criteria." on this line:
Set rs = qdf.OpenRecordset() '<----- SHOULD NOT PRODUCE ERROR :-)
When I mouseover rs, it equals Nothing.
Looks like a beer is due at lunch.....for both of us!
"Data type mismatch in criteria." on this line:
Set rs = qdf.OpenRecordset() '<----- SHOULD NOT PRODUCE ERROR :-)
When I mouseover rs, it equals Nothing.
Looks like a beer is due at lunch.....for both of us!
AARRGHH!! damn no text!
you need to post your table name, field names, and types of columns.
do that, and i guarantee i can give you working code.
until then try the following:
function testMainTable()
dim rs as recordset
set rs = currentdb.openrecordset("S
set rs = nothing
end function
dovholuk
you need to post your table name, field names, and types of columns.
do that, and i guarantee i can give you working code.
until then try the following:
function testMainTable()
dim rs as recordset
set rs = currentdb.openrecordset("S
set rs = nothing
end function
dovholuk
i know EE's been down. you have any luck TK421?
dovholuk
dovholuk
ASKER
I've been trying to get here all day! I'll work it and reply a.s.a.F.p.!! Thanks!
TK
TK
ASKER
dovholuk,
I have my present code under a command button. WHen doing so, it looks like this:
=======================
Private Sub Revise_Click()
bla
bla
bla
End Sub
=======================
Notice the "sub" instead of "Function". Am I proceding correctly placing the code under a command button? (using Private Sub Revise_Click().....)
Where should your code be placed?
function testMainTable()
dim rs as recordset
set rs = currentdb.openrecordset("S
set rs = nothing
end function
Meanwhile, I'll post my structure tomorrow EARLY (if EE is working!). Thanks!
I have my present code under a command button. WHen doing so, it looks like this:
=======================
Private Sub Revise_Click()
bla
bla
bla
End Sub
=======================
Notice the "sub" instead of "Function". Am I proceding correctly placing the code under a command button? (using Private Sub Revise_Click().....)
Where should your code be placed?
function testMainTable()
dim rs as recordset
set rs = currentdb.openrecordset("S
set rs = nothing
end function
Meanwhile, I'll post my structure tomorrow EARLY (if EE is working!). Thanks!
hiya TK421. i didn't mean to anger you regarding weather you've had any luck. i simply wanted EE to send you a message that would let you know that EE was back up. i hope you weren't terribly annoyed...
anyway, there is very little real difference between using function and sub. the MAIN difference (among other small ones) is that functions can return values. such as:
function TestingString() as string
TestingString = "Hi There"
end function
if you were to go to the debug window, and type
MsgBox TestingString
you'd see a messagebox saying "Hi There". sub's can't return values. returning values is necessary for building useful for building custom routines. instead of setting some global variable, you just return the variable to the calling procedure...
phew. so much for subs/functions. if you are testing the code with
private sub Command1_Click()
.....
.....
.....
end sub
that's fine. the whole purpose of my function was to see if you had problems other than the sql syntax. if it works/ed i'd be happy to know.
back to sub/function. there's two ways to try my code. first is to copy everything between the function/end function parts and put it inside your command button sub. such as:
private Revise_Click()
dim rs as recordset
set rs = currentdb.openrecordset("S
set rs = nothing
end sub
second, you can copy/paste my code into the same module and call it by using:
private Revise_Click()
testMainTable
end sub
either way you do it is fine....
look forward to seeing your table structure tomorrow...
dovholuk
anyway, there is very little real difference between using function and sub. the MAIN difference (among other small ones) is that functions can return values. such as:
function TestingString() as string
TestingString = "Hi There"
end function
if you were to go to the debug window, and type
MsgBox TestingString
you'd see a messagebox saying "Hi There". sub's can't return values. returning values is necessary for building useful for building custom routines. instead of setting some global variable, you just return the variable to the calling procedure...
phew. so much for subs/functions. if you are testing the code with
private sub Command1_Click()
.....
.....
.....
end sub
that's fine. the whole purpose of my function was to see if you had problems other than the sql syntax. if it works/ed i'd be happy to know.
back to sub/function. there's two ways to try my code. first is to copy everything between the function/end function parts and put it inside your command button sub. such as:
private Revise_Click()
dim rs as recordset
set rs = currentdb.openrecordset("S
set rs = nothing
end sub
second, you can copy/paste my code into the same module and call it by using:
private Revise_Click()
testMainTable
end sub
either way you do it is fine....
look forward to seeing your table structure tomorrow...
dovholuk
ASKER
I was in no way annoyed. If anything, annoyed with EE, but I'm sure their working on it. Sometimes email doesn't express our feelings to well. My apologies dovholuk. Again, in no way was I annoyed at you. ;-)
Cheers
TK
Cheers
TK
good to know TK421...
btw, EE is back up.
what the HELL are these people doing? if it's working... TEST it before you fix it, know what i mean? i've never had a site i run go down and i couldn't fix it. i wonder what they're doing...
*sigh*
dovholuk
btw, EE is back up.
what the HELL are these people doing? if it's working... TEST it before you fix it, know what i mean? i've never had a site i run go down and i couldn't fix it. i wonder what they're doing...
*sigh*
dovholuk
ASKER
Sorry dovholuk,
I took a 'sanity day' from work yesterday. Here's the structure:
Table Name: "Main Table"
There are many field names. I'll list a few here. Once I see your posted structure, I'll add the rest. Okay? Again, I do plan to eventually rename the items/field names to remove spaces, just haven't done that yet. I did not know of naming conventions when I started!
Field Names:
ID
Art Number
Charge Code
Model
Customer
Art Title
Is this all you need? THX!
I took a 'sanity day' from work yesterday. Here's the structure:
Table Name: "Main Table"
There are many field names. I'll list a few here. Once I see your posted structure, I'll add the rest. Okay? Again, I do plan to eventually rename the items/field names to remove spaces, just haven't done that yet. I did not know of naming conventions when I started!
Field Names:
ID
Art Number
Charge Code
Model
Customer
Art Title
Is this all you need? THX!
ASKER
Sorry dovholuk,
I took a 'sanity day' from work yesterday. Here's the structure:
Table Name: "Main Table"
There are many field names. I'll list a few here. Once I see your posted structure, I'll add the rest. Okay? Again, I do plan to eventually rename the items/field names to remove spaces, just haven't done that yet. I did not know of naming conventions when I started!
Field Names:
ID
Art Number (This is the only number field. All the others are text).
Charge Code
Model
Customer
Art Title
Is this all you need? THX!
I took a 'sanity day' from work yesterday. Here's the structure:
Table Name: "Main Table"
There are many field names. I'll list a few here. Once I see your posted structure, I'll add the rest. Okay? Again, I do plan to eventually rename the items/field names to remove spaces, just haven't done that yet. I did not know of naming conventions when I started!
Field Names:
ID
Art Number (This is the only number field. All the others are text).
Charge Code
Model
Customer
Art Title
Is this all you need? THX!
no... the ONLY problem you're having is with SQL so i'll repost the "issue":
SELECT TOP 1 *
FROM [Main Table]
WHERE [Art Number] = 'some art number'
ORDER BY [Date Submitted To Art Dept] DESC
what i need to see in your table is the [date submitted to art dept] part of your table, that and the [art number] (you've given me [art number]). the problem is that the [date submitted to art dept] probably is NOT a field in your table...
that known, try the code i gave you with this minor change and see if it works.
'=========================
qdf.sql = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = '" & ArtLogNumber & "'
'=========================
if you take the Order By clause out of your statement, it WILL work, trust me...
so that said, either post the date field here (so i can give you the order by clause) or add the order by yourself.
let me know what you end up doing...
we are close now. good luck. i'm sure you'll get it! :)
dovholuk
SELECT TOP 1 *
FROM [Main Table]
WHERE [Art Number] = 'some art number'
ORDER BY [Date Submitted To Art Dept] DESC
what i need to see in your table is the [date submitted to art dept] part of your table, that and the [art number] (you've given me [art number]). the problem is that the [date submitted to art dept] probably is NOT a field in your table...
that known, try the code i gave you with this minor change and see if it works.
'=========================
qdf.sql = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = '" & ArtLogNumber & "'
'=========================
if you take the Order By clause out of your statement, it WILL work, trust me...
so that said, either post the date field here (so i can give you the order by clause) or add the order by yourself.
let me know what you end up doing...
we are close now. good luck. i'm sure you'll get it! :)
dovholuk
ASKER
[Date Submitted To Art Dept] IS a field in [Main Table]. If I do not include this ORDER By clause, i may get a record that is not the most recent = very bad. So the modified code with this clause will look like....?? :)
ok... i created a sample database.
http://www.geocities.com/dovholuk/tk421.mdb
open that database up and check out what's there. there's a form called form1. open the form and click the "click me" button. it will dump you to code. step through each line and you'll see that the place where there's an error for you, there's no error for me...
try putting some of your sample data into this database and see if it works...
i don't know what to tell you. i'd say that i spelled [Date Submitted To Art Dept] differently...
check out what i put there and get back to me...
dovholuk
http://www.geocities.com/dovholuk/tk421.mdb
open that database up and check out what's there. there's a form called form1. open the form and click the "click me" button. it will dump you to code. step through each line and you'll see that the place where there's an error for you, there's no error for me...
try putting some of your sample data into this database and see if it works...
i don't know what to tell you. i'd say that i spelled [Date Submitted To Art Dept] differently...
check out what i put there and get back to me...
dovholuk
ASKER
Akk! The 'Click Me' field has no event procedure under it! It's not doing anything. Is that what you expected?
TK
TK
hmmm. i must have uploaded it BEFORE i saved it...
DOLT! :)
here's what should be under the button:
Private Sub Command0_Click()
TestCodeSnippet
End Sub
(just one line)
dovholuk
DOLT! :)
here's what should be under the button:
Private Sub Command0_Click()
TestCodeSnippet
End Sub
(just one line)
dovholuk
ASKER
No luck. Here's what I've tried:
1) Copied MY [Main Table] into your database - same error
2) Pasted your latest code into my database - same error
3) Moved my code to the "Modules" Tab and called it (like your DB) - same error
Something with my [Main Table] (I guess) is creating this datatype mismatch error. Any suggestions? I guess I could really simplify my DB and get it to you somehow...
TK
1) Copied MY [Main Table] into your database - same error
2) Pasted your latest code into my database - same error
3) Moved my code to the "Modules" Tab and called it (like your DB) - same error
Something with my [Main Table] (I guess) is creating this datatype mismatch error. Any suggestions? I guess I could really simplify my DB and get it to you somehow...
TK
ok so i'm gonna go shoot myself now....
you're getting a datatype error? damn i wish i'd seen that earlier. i'm sure you posted it above, i just didn't read it....
try this code then:
qdf.SQL = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;"
i removed the parentheses from around the artlognumber. the parentheses indicate text, your artlognumber is stored as a number, right???
sorry for not noticing that sooner.
dovholuk
you're getting a datatype error? damn i wish i'd seen that earlier. i'm sure you posted it above, i just didn't read it....
try this code then:
qdf.SQL = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;"
i removed the parentheses from around the artlognumber. the parentheses indicate text, your artlognumber is stored as a number, right???
sorry for not noticing that sooner.
dovholuk
ASKER
Okay. We have progress! Woohoo!
Getting this error:
Syntax error (missing operator) in query expression '[Art Number]='.
on this line:
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
Any clue?
Here's the whole code thusfar:
==========================
Sub LeadWriterRevise()
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Dim ArtLogNumber As String
'Stop
Set db = CurrentDb
Set qdf = db.CreateQueryDef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
'art log number was entered.
ArtLogNumber = IIf(Left(ArtLogNumber, 1) = "a", Mid(ArtLogNumber, 2), ArtLogNumber)
qdf.SQL = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;"
Set rs = qdf.OpenRecordset() '<----- SHOULD NOT PRODUCE ERROR :-)
End If
'=========================
DoCmd.Close acForm, "Create New Request Form LeadWriters"
'Validation here
If rs.RecordCount < 1 Then
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
GoTo AddNewFields
End If
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
If (Nz(varIsApproved, "") <> "") Then 'If Writer Approval date is not equal to null then
DoCmd.OpenForm "Writer Lead Data Entry Form"
Set rs2 = Forms![Writer Lead Data Entry Form].RecordsetClone
'open Form and copy some fields from original recordset to the new record
rs2.AddNew
rs2![AECMA Number] = rs![AECMA Number]
rs2![Customer] = rs![Customer]
rs2![Model] = rs![Model]
rs2![Manual] = rs![Manual]
rs2![Chapter] = rs![Chapter]
rs2![Section] = rs![Section]
Else
MsgBox "This Art Log Number is currently being worked. Please see the Illustration Department for assistance.", vbInformation, "The Boeing Company - CH-47 Illustrations Tracking System"
DoCmd.OpenForm "Writer Lead Data Entry Form", acNormal, , , acFormAdd
End If
'copy fields from the record found to a new record
'something like this
AddNewFields:
[Forms]![Writer Lead Data Entry Form]![Author] = MyCurrentUser()
[Forms]![Writer Lead Data Entry Form]![Extension] = MyPhone()
[Forms]![Writer Lead Data Entry Form]![Art Number] = "0"
[Forms]![Writer Lead Data Entry Form]![Date Submitted To Art Dept] = Date
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![Art Number].Enabled = False
[Forms]![Writer Lead Data Entry Form]![Art Number].Locked = True
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![CopyRecord].Visible
[Forms]![Writer Lead Data Entry Form]![Revise].Visible = True
DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
End1:
End Sub
==========================
Getting this error:
Syntax error (missing operator) in query expression '[Art Number]='.
on this line:
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
Any clue?
Here's the whole code thusfar:
==========================
Sub LeadWriterRevise()
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Dim ArtLogNumber As String
'Stop
Set db = CurrentDb
Set qdf = db.CreateQueryDef("") 'makes a "temporary" query
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
'art log number was entered.
ArtLogNumber = IIf(Left(ArtLogNumber, 1) = "a", Mid(ArtLogNumber, 2), ArtLogNumber)
qdf.SQL = "SELECT TOP 1 [Main Table].* " _
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;"
Set rs = qdf.OpenRecordset() '<----- SHOULD NOT PRODUCE ERROR :-)
End If
'=========================
DoCmd.Close acForm, "Create New Request Form LeadWriters"
'Validation here
If rs.RecordCount < 1 Then
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
GoTo AddNewFields
End If
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
If (Nz(varIsApproved, "") <> "") Then 'If Writer Approval date is not equal to null then
DoCmd.OpenForm "Writer Lead Data Entry Form"
Set rs2 = Forms![Writer Lead Data Entry Form].RecordsetClone
'open Form and copy some fields from original recordset to the new record
rs2.AddNew
rs2![AECMA Number] = rs![AECMA Number]
rs2![Customer] = rs![Customer]
rs2![Model] = rs![Model]
rs2![Manual] = rs![Manual]
rs2![Chapter] = rs![Chapter]
rs2![Section] = rs![Section]
Else
MsgBox "This Art Log Number is currently being worked. Please see the Illustration Department for assistance.", vbInformation, "The Boeing Company - CH-47 Illustrations Tracking System"
DoCmd.OpenForm "Writer Lead Data Entry Form", acNormal, , , acFormAdd
End If
'copy fields from the record found to a new record
'something like this
AddNewFields:
[Forms]![Writer Lead Data Entry Form]![Author] = MyCurrentUser()
[Forms]![Writer Lead Data Entry Form]![Extension] = MyPhone()
[Forms]![Writer Lead Data Entry Form]![Art Number] = "0"
[Forms]![Writer Lead Data Entry Form]![Date Submitted To Art Dept] = Date
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![Art Number].Enabled = False
[Forms]![Writer Lead Data Entry Form]![Art Number].Locked = True
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![CopyRecord].Visible
[Forms]![Writer Lead Data Entry Form]![Revise].Visible = True
DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
End1:
End Sub
==========================
in your code, you have NOT set NewRec to anything yet. thus when the DLOOKUP occurs, it uses NULL as NewRec... this is what's causing the error.
if you initialize NewRec you should be fine. since you dim it as variant, it won't have been initialized. that should clear up your problem.
that should get you going...
let me know if you need more help.
dovholuk
if you initialize NewRec you should be fine. since you dim it as variant, it won't have been initialized. that should clear up your problem.
that should get you going...
let me know if you need more help.
dovholuk
here's how i'd do what your original question stated:
Dim rs As Recordset
Dim ArtLogNumber As String
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
'art log number was entered.
ArtLogNumber = IIf(Left(ArtLogNumber, 1) = "a", Mid(ArtLogNumber, 2), ArtLogNumber)
'1) Open a recordset from a stored query.
Set rs = CurrentDb.OpenRecordset("S
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;")
'part 1 accomplished - just a little differently
'2) If recordset finds a record, continue. If recordset is empty,
' display msgbox. QUIT.
If rs.EOF Then
'false part 2
'display a messagebox
MsgBox "You silly bastard. no record found"
'quit
Exit Function
End If
'part 2 complete
'3) If a field [DatePoscripted] in that record is not Null, continue,
' else display msgbox "Existing record not completed..."
If IsNull(rs![Date Poscripted]) = True Then
'part 3
MsgBox "Existing record not completed..."
Exit Function
End If
'part 3 complete
'4) If record does exist AND is completed, then
' copy some fields to the new record.
'i would run an action query vs. creating a new recordset and using .edit/.add etc. like:
CurrentDb.Execute "INSERT INTO [Main Table] Field1, Field2, Field3 " _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
End If
i'm sorry if you need to move some text around so that you don't get errors when you copy/paste...
dovholuk
Dim rs As Recordset
Dim ArtLogNumber As String
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
'art log number was entered.
ArtLogNumber = IIf(Left(ArtLogNumber, 1) = "a", Mid(ArtLogNumber, 2), ArtLogNumber)
'1) Open a recordset from a stored query.
Set rs = CurrentDb.OpenRecordset("S
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;")
'part 1 accomplished - just a little differently
'2) If recordset finds a record, continue. If recordset is empty,
' display msgbox. QUIT.
If rs.EOF Then
'false part 2
'display a messagebox
MsgBox "You silly bastard. no record found"
'quit
Exit Function
End If
'part 2 complete
'3) If a field [DatePoscripted] in that record is not Null, continue,
' else display msgbox "Existing record not completed..."
If IsNull(rs![Date Poscripted]) = True Then
'part 3
MsgBox "Existing record not completed..."
Exit Function
End If
'part 3 complete
'4) If record does exist AND is completed, then
' copy some fields to the new record.
'i would run an action query vs. creating a new recordset and using .edit/.add etc. like:
CurrentDb.Execute "INSERT INTO [Main Table] Field1, Field2, Field3 " _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
End If
i'm sorry if you need to move some text around so that you don't get errors when you copy/paste...
dovholuk
ASKER
dovholuk,
Your modified code looks great. Can you help me with the last part of it? This line:
CurrentDb.Execute "INSERT INTO [Main Table] ID, Art Number, Model" _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
As you can see, I replaced Field1, Field2, Field3 with a few fields in the [Main Table]. However, what do I place in these locations?
rs!textField
rs!numericField
rs!DateField
Am I on the right track? Mega thanks!
TK421
Your modified code looks great. Can you help me with the last part of it? This line:
CurrentDb.Execute "INSERT INTO [Main Table] ID, Art Number, Model" _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
As you can see, I replaced Field1, Field2, Field3 with a few fields in the [Main Table]. However, what do I place in these locations?
rs!textField
rs!numericField
rs!DateField
Am I on the right track? Mega thanks!
TK421
you are 100% on the right track.
how about a short dissertation?
i like using insert statements over rs.addnew. here's why:
1.) it's 1000% easier to code
there it is. that's it. it is also good to note though, that using the recordset and .addnew ing isn't really bad. the reality is that you'll never notice the difference so whichever method you perfer is best.
the PROBLEM with using an insert statement, is that if your users ever use apostrophes (like O'Keefe, O'Henry,etc.) you'll run into issues with the insert statement.
imagine this SQL:
INSERT into MyTable (SomeTextField) Values('Jimmy Smits')
this will function perfectly fine when you use:
currentdb.execute "INSERT into MyTable (SomeTextField) Values('Jimmy Smits')"
however, if you use "Georgia O'Keefe" you'll get:
currentdb.execute "INSERT into MyTable (SomeTextField) Values('Georgia O'Keefe')"
see the problem? access will think that your value is only "Georgia O" and it will crash because Keefe' doesn't have a open or closing apostrophe... so you need to double the apostrophes... essentiall you WANT to use
currentdb.execute "INSERT into MyTable (SomeTextField) Values('Georgia O''Keefe')"
note the two apostrophes by O''Keefe.
basically what i'm saying is that until you start coding your own queries, i think it'd be safer, and smarter for you to use the recordset.addnew, recordset.edit etc. methods of the recordset object... sorry to lead you down a "wrong road" but at least you know there are alternatives to everything, right! :)
that said, i would replace:
CurrentDb.Execute "INSERT INTO [Main Table] ID, Art Number, Model" _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
with:
dim strID as string
dim lngArtNumber as long
dim dteModel as date
strid = rs!id
lngArtNumber = rs![Art Number]
dteModel = rs!Model
rs.addnew
rs!id = strID
rs![Art Number] = lngArtNumber
rs!dteModel = dteModel
rs.update
i've uploaded another sample to:
http://www.geocities.com/dovholuk/tk421_3.mdb
let me know if you need more help
dovholuk
how about a short dissertation?
i like using insert statements over rs.addnew. here's why:
1.) it's 1000% easier to code
there it is. that's it. it is also good to note though, that using the recordset and .addnew ing isn't really bad. the reality is that you'll never notice the difference so whichever method you perfer is best.
the PROBLEM with using an insert statement, is that if your users ever use apostrophes (like O'Keefe, O'Henry,etc.) you'll run into issues with the insert statement.
imagine this SQL:
INSERT into MyTable (SomeTextField) Values('Jimmy Smits')
this will function perfectly fine when you use:
currentdb.execute "INSERT into MyTable (SomeTextField) Values('Jimmy Smits')"
however, if you use "Georgia O'Keefe" you'll get:
currentdb.execute "INSERT into MyTable (SomeTextField) Values('Georgia O'Keefe')"
see the problem? access will think that your value is only "Georgia O" and it will crash because Keefe' doesn't have a open or closing apostrophe... so you need to double the apostrophes... essentiall you WANT to use
currentdb.execute "INSERT into MyTable (SomeTextField) Values('Georgia O''Keefe')"
note the two apostrophes by O''Keefe.
basically what i'm saying is that until you start coding your own queries, i think it'd be safer, and smarter for you to use the recordset.addnew, recordset.edit etc. methods of the recordset object... sorry to lead you down a "wrong road" but at least you know there are alternatives to everything, right! :)
that said, i would replace:
CurrentDb.Execute "INSERT INTO [Main Table] ID, Art Number, Model" _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
with:
dim strID as string
dim lngArtNumber as long
dim dteModel as date
strid = rs!id
lngArtNumber = rs![Art Number]
dteModel = rs!Model
rs.addnew
rs!id = strID
rs![Art Number] = lngArtNumber
rs!dteModel = dteModel
rs.update
i've uploaded another sample to:
http://www.geocities.com/dovholuk/tk421_3.mdb
let me know if you need more help
dovholuk
ASKER
Oh my gawd, I think I have it. testing...
ASKER
I modified the code a little. I changed a few of the variables to strings and removed the ID field - it's the NO DUPLICATES, primary key field. So it now creates a record in the table, but I'd like the code to open a form and populate the fields in this new record. How is this handled? NOTICE the needed code separated with '*********** STARS below. I hope it makes sense.
Here is the latest code:
Sub WtrRevReq()
Dim rs As Recordset
Dim ArtLogNumber As String
Dim strID As String
Dim ZArtNumber As String
Dim ZModel As String
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
'art log number was entered.
ArtLogNumber = CLng(IIf(Left(ArtLogNumber
'1) Open a recordset from a stored query.
Set rs = CurrentDb.OpenRecordset("S
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;")
'part 1 accomplished - just a little differently
'2) If recordset finds a record, continue. If recordset is empty,
' display msgbox. QUIT.
If rs.EOF Then
'false part 2
'display a messagebox
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
DoCmd.OpenForm "Writer Lead Data Entry Form", acNormal, , , acFormAdd
GoTo EndOfCode
'quit
Exit Sub
End If
'part 2 complete
'3) If a field [DatePoscripted] in that record is not Null, continue,
' else display msgbox "Existing record not completed..."
If IsNull(rs![Date Poscripted]) = True Then
'part 3
MsgBox "The previous Request for this Art Log Number is not completed by the Graphic Designer. Please see the Art Dept.", vbInformation, "Previous Request not yet completed"
'*************************
'NEED SOMETHING HERE LIKE THIS
'Where it opens the new record with the copied fields being populated
' Else
'DoCmd.OpenForm "Writer Lead Data Entry Form", acNormal, , , acFormAdd
'*************************
strID = rs!ID
ZArtNumber = rs![Art Number]
ZModel = rs!Model
rs.AddNew
' rs!ID = strID
rs![Art Number] = ZArtNumber
rs!Model = ZModel
rs.Update
MsgBox "copy complete"
End If
Exit Sub
End If
'part 3 complete
'4) If record does exist AND is completed, then
' copy some fields to the new record.
'i would run an action query vs. creating a new recordset and using .edit/.add etc. like:
' CurrentDb.Execute "INSERT INTO [Main Table] Field1, Field2, Field3 " _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
EndOfCode:
[Forms]![Writer Lead Data Entry Form]![Author] = MyCurrentUser()
[Forms]![Writer Lead Data Entry Form]![Extension] = MyPhone()
[Forms]![Writer Lead Data Entry Form]![Art Number] = "0"
[Forms]![Writer Lead Data Entry Form]![Date Submitted To Art Dept] = Date
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![Art Number].Enabled = False
[Forms]![Writer Lead Data Entry Form]![Art Number].Locked = True
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![CopyRecord].Visible
[Forms]![Writer Lead Data Entry Form]![Revise].Visible = True
End Sub
Here is the latest code:
Sub WtrRevReq()
Dim rs As Recordset
Dim ArtLogNumber As String
Dim strID As String
Dim ZArtNumber As String
Dim ZModel As String
ArtLogNumber = InputBox("Enter the Art Log Number", , "Value Required")
If ArtLogNumber = "" Then
'user didn't enter an art log number...
MsgBox "A value is required", vbCritical, "No Value Entered"
Exit Sub
Else
'art log number was entered.
ArtLogNumber = CLng(IIf(Left(ArtLogNumber
'1) Open a recordset from a stored query.
Set rs = CurrentDb.OpenRecordset("S
& "FROM [Main Table] " _
& "WHERE [Main Table].[Art Number] = " & ArtLogNumber & " " _
& "ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;")
'part 1 accomplished - just a little differently
'2) If recordset finds a record, continue. If recordset is empty,
' display msgbox. QUIT.
If rs.EOF Then
'false part 2
'display a messagebox
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
DoCmd.OpenForm "Writer Lead Data Entry Form", acNormal, , , acFormAdd
GoTo EndOfCode
'quit
Exit Sub
End If
'part 2 complete
'3) If a field [DatePoscripted] in that record is not Null, continue,
' else display msgbox "Existing record not completed..."
If IsNull(rs![Date Poscripted]) = True Then
'part 3
MsgBox "The previous Request for this Art Log Number is not completed by the Graphic Designer. Please see the Art Dept.", vbInformation, "Previous Request not yet completed"
'*************************
'NEED SOMETHING HERE LIKE THIS
'Where it opens the new record with the copied fields being populated
' Else
'DoCmd.OpenForm "Writer Lead Data Entry Form", acNormal, , , acFormAdd
'*************************
strID = rs!ID
ZArtNumber = rs![Art Number]
ZModel = rs!Model
rs.AddNew
' rs!ID = strID
rs![Art Number] = ZArtNumber
rs!Model = ZModel
rs.Update
MsgBox "copy complete"
End If
Exit Sub
End If
'part 3 complete
'4) If record does exist AND is completed, then
' copy some fields to the new record.
'i would run an action query vs. creating a new recordset and using .edit/.add etc. like:
' CurrentDb.Execute "INSERT INTO [Main Table] Field1, Field2, Field3 " _
& "VALUES ('" & rs!textField & "', " & rs!numericField & ", #" & rs!DateField & "#)"
EndOfCode:
[Forms]![Writer Lead Data Entry Form]![Author] = MyCurrentUser()
[Forms]![Writer Lead Data Entry Form]![Extension] = MyPhone()
[Forms]![Writer Lead Data Entry Form]![Art Number] = "0"
[Forms]![Writer Lead Data Entry Form]![Date Submitted To Art Dept] = Date
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![Art Number].Enabled = False
[Forms]![Writer Lead Data Entry Form]![Art Number].Locked = True
[Forms]![Writer Lead Data Entry Form]![Art Classification] = 2
[Forms]![Writer Lead Data Entry Form]![CopyRecord].Visible
[Forms]![Writer Lead Data Entry Form]![Revise].Visible = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dovholuk,
Yes dovholuk, you have provided more than expected help with my question(s). The trial databases, multiple issues, etc. And yes, to me it is one problem - (a process not working), but I do realize the multiple involved issues I threw at you. In return, I will post another Q and pass additional points for your gracious assistance.
Thanks.
TK421
Yes dovholuk, you have provided more than expected help with my question(s). The trial databases, multiple issues, etc. And yes, to me it is one problem - (a process not working), but I do realize the multiple involved issues I threw at you. In return, I will post another Q and pass additional points for your gracious assistance.
Thanks.
TK421
if you are using a2k, do you have a reference to DAO set?
your rs is declared without ADODB.recordset or DAO.recordset. if you have no reference to DAO, and you have a reference to ADO, this will always cause an error.
just a thought... i'll continue to peruse your code...
dovholuk