We help IT Professionals succeed at work.

VB / SQL Recordset Validation

TK421
TK421 asked
on
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(dbOpenDynaset)
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")
   
'------------------------------WORKS TO HERE
    Set rs = qdf.OpenRecordset(dbOpenDynaset)   '<-----  PRODUCES ERROR
   
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].RecordsetClone

        '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] = MyPhone()
[Forms]![Form1]![Art Number] = "0"

DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
   
End1:
End Sub
Comment
Watch Question

CERTIFIED EXPERT

Commented:
just real quick. are you using access97 or a2k?

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

Author

Commented:
A97 and I'm new to DAO. YIKES!
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
if you're using a97 you'll be all set with the reference.

instead of using:
   Set rs = qdf.OpenRecordset(dbOpenDynaset)   '<-----  PRODUCES ERROR

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

Commented:
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

Author

Commented:
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(dbOpenDynaset)
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

CERTIFIED EXPERT

Commented:
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("SELECT TOP 1 * " _
   & "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

Commented:
Make sure that every parameter in the stored query are in the same line

CERTIFIED EXPERT

Commented:
"TK421, why aren't you at your post?"

dovholuk

Author

Commented:
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

Author

Commented:
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?
CERTIFIED EXPERT

Commented:
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],1)="a",Mid([LogNum],2),[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",Mid([LogNum],2),[LogNum]
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
CERTIFIED EXPERT

Commented:
any progress TK421?

dovholuk

Author

Commented:
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],1)="a",Mid([LogNum],2),[LogNum])))
ORDER BY [Date Submitted To Art Dept] DESC;


TK
CERTIFIED EXPERT

Commented:
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")
   
'------------------------------WORKS TO HERE
   Set rs = qdf.OpenRecordset(dbOpenDynaset)   '<-----  PRODUCES ERROR
==============================================================================

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
      '------------------------------WORKS TO HERE
      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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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



CERTIFIED EXPERT

Commented:
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

Author

Commented:
Cool. I'm off to lunch. I'll try and reply after...thanks!

Author

Commented:
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!
CERTIFIED EXPERT

Commented:
CERTIFIED EXPERT

Commented:
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("SELECT * FROM [Main Table]")
set rs = nothing
end function

dovholuk
CERTIFIED EXPERT

Commented:
i know EE's been down. you have any luck TK421?

dovholuk

Author

Commented:
I've been trying to get here all day! I'll work it and reply a.s.a.F.p.!! Thanks!

TK

Author

Commented:
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("SELECT * FROM [Main Table]")
set rs = nothing
end function

Meanwhile, I'll post my structure tomorrow EARLY (if EE is working!). Thanks!
CERTIFIED EXPERT

Commented:
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("SELECT * FROM [Main Table]")
      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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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!

Author

Commented:
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!
CERTIFIED EXPERT

Commented:
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

Author

Commented:
[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....??  :)
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Akk! The 'Click Me' field has no event procedure under it! It's not doing anything. Is that what you expected?

TK
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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 = False
[Forms]![Writer Lead Data Entry Form]![Revise].Visible = True

DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
   
End1:
End Sub
================================================
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
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("SELECT TOP 1 [Main Table].* " _
      & "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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Oh my gawd, I think I have it. testing...

Author

Commented:
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) = "a", Mid(ArtLogNumber, 2), ArtLogNumber))
 
 
 
  '1) Open a recordset from a stored query.
  Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 [Main Table].* " _
     & "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 = False
[Forms]![Writer Lead Data Entry Form]![Revise].Visible = True

End Sub
CERTIFIED EXPERT
Commented:
you've gotten a lot of mileage out of me on this question and what you're askin' for seems a little different than the original question. EE likes for its users to ask separate questions.  but since i've come this far, i'll get you started...

first, without seeing your db, it's really hard to know the flow of the app. so i can only speculate as to doing this. this is my best shot:

1.) open the form based on the table
2.) jump to the newly created record (acLast)
http://www.geocities.com/dovholuk/tk421_4.mdb

i hope this resolves everything for you.

please note, this is just ONE way to do this. there are other ways but this seems simple and works nicely for what i see your need to be.

good luck.  

dovholuk

Author

Commented:
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