Validation before executing query 2

[NKA]

I have an unbound form which the user selects criteria for a report output.

If the user enters a 7 digit number in TXT_Parent (an unbound field), I want to be able to check if the first 4 digits match any of the records in a table (TBL_SelCon) which is used for a (hidden at the time) combo box (CBO_ConList) on the form.

I tried the following:
if left((TXT_Parent),4) <> [TBL_SelCon]![Contract_Code] Then
Msgbox &quot;This is an invalid number&quot; blah blah blah
Else
Proceed.....
End if

An error message saying the table cannot be found - because it's not open I suppose? Would anyone know how I can get this to work or am I asking the impossible??

Many thanks

NKA

The answer's always easy - if you know it! ;-)

 

[RoyVidar]

Hi!

Assuming DAO, something like this might be used:

dim db as dao.database
dim rs as dao.recordset

set db=db.openrecordset(&quot;Select Contract_Code from TBL_SelCon where Contract_Code = '&quot; & left(me!txt_parent,4) & &quot;'&quot;, adopendynamic)
if rs.bof then
msgbox &quot;Invalid number&quot;
...

rs.close
set rs=nothing
set db=nothing

Now, this is assuming the Contract_Code is text. If it's numeric, you'll need to change it to something like this:
&quot;Select Contract_Code from TBL_SelCon where Contract_Code = &quot; & cint(left(me!txt_parent,4))

Note - I haven't tested this, and if you're using Access 2000+, you'll need to go to Tools | References in any Module and check the Microsoft DAO 3.6 Object Library

HTH Roy-Vidar

 

[RoyVidar]

Oups:

Saw after posting that what was supposed to be on one line, was wrapped to two. Pls put all of the following on one line:

set db=db.openrecordset(&quot;Select Contract_Code from TBL_SelCon where Contract_Code = '&quot; & left(me!txt_parent,4) & &quot;'&quot;, adopendynamic)

Or break it up like i e this

set db=db.openrecordset(&quot;Select Contract_Code from TBL_SelCon where &quot; & _
&quot;Contract_Code = '&quot; & left(me!txt_parent,4) & &quot;'&quot;, adopendynamic)

Sorry, Roy-Vidar

 

[BillPower]

Hi NKA,

Try this if Contract_Code is a string/text:
If DCount(&quot;[Contract_Code]&quot;, &quot;TBL_SelCon&quot;, &quot;[Contract_Code]) = '&quot; & strLookUp & &quot;'&quot = 0 Then

Otherwise if it's a number:
If DCount(&quot;[Contract_Code]&quot;, &quot;TBL_SelCon&quot;, &quot;[Contract_Code]) = &quot; & strLookUp) = 0 Then

Bill

 

[BillPower]

Hi Roy,

I forgot to refresh before submitting, your's should do the trick as well.

Good Luck

Bill

 

[NKA]

Hi guys!
Thank you for your help on this one - however, I have hit a brick wall

Firstly, I am using Access 97. When I insert the code and check it, it tells me variable not defined with the adopendynamic.... oops.

Secondly, when I tried the other suggestion, that tells me variable not defined for strLookup.

Not being a whizz with coding - I haven't a clue how to fix either of them!!!

Cheers for your help

NKA

The answer's always easy - if you know it! ;-)

 

[RoyVidar]

Hi!

In Bill's code you'd probably put the following lines above his code:

dim strLookup as string ' declare variable
strLookup = left(me!txt_parent,4)

and follow Bill's advice with strings/numerics

I'm gonna check my syntax and report back

Roy-Vidar

 

[RoyVidar]

Follow up: Aaarrrgh!

I've been mixing up different versions. Sorry!

Your version needs the:

[tt]dbOpenDynamic[/tt]

constant (adopendynamic is used in my version)

To make another correction: I should rather have suggested the dbOpenForwardOnly, not the dbOpenDynamic. The latter opens a recordset for &quot;manipulation&quot; while the first for &quot;browsing&quot;, and that's enough in your case.

HTH Roy-Vidar

 

[NKA]

Roy - You're a Star!

I am still getting an error message when I try to run your code - but Bill's works a treat - so that'll do me!

Thank you both - you can both have a star.

Cheers



NKA

The answer's always easy - if you know it! ;-)

 

[NKA]

Uh-oh... something's still not right! I have changed the way I collect this information slightly, but do not understand why the code Bill gave me doesn't work (even after slight adaptation).

Rather than looking at a filtered recordset, I need to look at the complete recordset. User enters a 7-digit number (unbound text field called TXT_Parent). I need to check if this number exists in a field called PARENT_CODE (Table is TBL_TEMPCons).

If it does then open the report, otherwise message....

Can someone please advise where I've gone wrong and put me back on the right track? Many thanks!

NKA

The answer's always easy - if you know it! ;-)

 

[RoyVidar]

Hi!

Thanx for the star!

I think it should be something like this:

[tt]If DCount(&quot;[PARENT_CODE]&quot;, &quot;TBL_TEMPCons&quot;, &quot;[PARENT_CODE]) = '&quot; & me!TXT_Parent & &quot;'&quot = 0 Then[/tt]

(all on one line)

If this doesn't do it, please consider posting your current code, where it halts, what errormessage/number if available.

HTH Roy-Vidar

 

[NKA]

Roy!!! Have another star! That hit the nail on the head a treat (after I removed the extra bracket in the code!!).

At last... cooking with gas.
Cheers




NKA

The answer's always easy - if you know it! ;-)