Field Compare... 2

[Junior1544]

How to do compare a field on a form, to a field in a query?? I want to control if the data is valid. If the data in the field on the form is the same as any data in a query, then it's not valid, if it isn't in the query, the data on the form is valid. If you need any more information let me know... I've been working on this for over 2 weeks now and can't figure it out...

Thanks
--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[Junior1544]

Private Sub VNUM_LostFocus()
Dim rst As DAO.Recordset
Dim frm As Form


Set frm = Forms!VISITOR
Set rst = CurrentDb.OpenRecordset("ACTIVEQUERY&quot

If IsNull(frm!VNUM) Then
'MsgBox ("null found&quot
Else
rst.FindFirst "VNUM='" & frm!VNUM & "'"

If rst.NoMatch Then
'MsgBox ("good&quot
Else
MsgBox ("Bad Data.&quot
Me.VNUM = Null
DoCmd.CancelEvent
End If

End If

rst.Close
Set rst = Nothing
Set frm = Nothing
End Sub


This took me a few days to customize to exacly this... checking for null's and all... the hardest time i had was figuring out how to get what was given me to accept text. The example given me would only take numbers.. (easy mis-understanding given myu field names)

I'm very glad for all the help, it is now working great!

Thank you.
--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[WildHare]

I don't mean to break in here, especially since it seems like you're both almost there, but I think there's an easier way.

If I understand the thread, if Forms!VisitorNumber is in your query-recordsource, then it's a BADDIE, otherwise it's a GOODIE, right? Your query uses a table called tblVisitor as the source?

If Vnum is the primary key (which it seems to be, given the reference in your code to "rst.Index", the DLOOKUP is probably the fastest way to see if it's there.
[red]
Sub VNUM_Exit

if IsNull(Dlookup(Me!Vnum, "tblVisitors&quot) then
GOODIE
else
BADDIE
end if
[/red]





Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[Junior1544]

well, i'm looking for more then just notnull...

i'm also looking to make sure not to make duplicates in a query... being that this form is based on a table, and the query is where i need to check... (the query is based on the table and limites the amount of data that i'm checking against)... if i were the only one working on this database i would just remake the form and base it on the query instead of on the table, but i'm one of a few that is working on it... so i can't make a big change without another's permision...

the code you have there looks like it's just checking to see if it's null or not...

thanks...
--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[WildHare]

I thought you were looking to see if a user-entered visitor number was in the query-source already? If it isn't, the dlookup will return Null. Simple.

I can almost guarantee you that FINDFIRST will be less efficient than DLOOKUP.

I just can't see the overhead of setting up another recordsource and FINDFIRST'ing through it.

I might just be a blowhard here, but I hate to see people tie themselves in knots to code VBA solutions to a problem that can be handled much more directly with a single function call.

Of course, the REAL answer to this problem is to not have the user enter the number in the first place - if you need to make sure that a value doesn't already exist when you go to make a new record, you should probably table-drive the generation of a visitor number and take all possible errors OUT of the hands of the user.




Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[Junior1544]

ok, i must be mis-understanding dlookup...

the problem i'm having is it's not just a number, it's a text field... is that little bit of code able to do the same as that whole sting of vb that i have do pretty much the same thing??

--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[KyleS]

Junior,
Yes and No, a ver similar thing can be done using DLookup, but it's not much less complicated and on a really large table won't be any faster (could be slower - Domain Aggregates are notoriously slow).

But if you put it in like this:

if IsNull(Dlookup(Me!Vnum, "tblVisitors&quot) then
GOODIE
else
BADDIE
end if

You will never get a "Null" back, since all it will do is look up the value on the form and return it to you (so if the field isn't null, you'll get something in return). You will need something more like this:

If IsNull(frm!VNUM) Then
'MsgBox ("null found&quot
Else
If IsNull(DLookup("[VNUM]", "[ACTIVEQUERY]", "[VNUM] = " & frm!VNUM)) Then
Msgbox "Good"
Else
Msgbox "Bad"
End If
End If



Kyle :

 

[WildHare]

Kyle - yes, you are of course correct. My snippet was syntactically imprecise, not to say linguistically incomplete and lexigraphically inadequate.

My point was that rather than looking through a recordset to see if something is NOT there, you're better off automating the entry of the value, if it's at all possible.

Thanks for checking up on me, though. Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[KyleS]

Jim,
Nicely put (I had to look up lexicographically )

I agree, as I hinted to in one of my earlier posts, that if this fields needs to be unique, it should at least be indexed as such. Although I also agree that if at all possible the unique field should be automated, I don't believe Junior has that option in this case.

Junior, any more questions on the subject? Kyle :

 

[Junior1544]

I'm just going to be looking into the use of dlookup to see if it will fill my needs... right now i have the rst.fastfind in place and running, but i would like to reduce the amount of code used to do the same thing... as to automating the data input, i can't, it's for an inventory control porion of my database, and each item i have has a different number(and some letters)... i am concerned with dlookup taking longer, because it does pause a little longer then i'd like... alwell... thanks for all the help, and pointing me in a better direction...

--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[WildHare]

as to automating the data input, i can't,

Never say "I Can't"...if you analyze the situation sufficiently, you should be able to remove the element of random error from it.



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[Junior1544]

wel,l as i said, it's an inventory control database... (well, at least this part is...) one of the rules i was given is that the database can't tell the person what to give, because the database doesn't make the decisions... it just holds the data for the users to be able to make a decision as to what items to issue to what people... we have multiple of each items... so it would go against my suporvisor to automate that field... i tried, that would have made it easier for me... alwell...

thanks for that suggestion though, it did get me thinking about another smaller problem i'm having...

thank you so much for all the help guys and gals...

jim, you've answered alot of my questions... even befor i post them... (i try to search for an answer befor i post) thanks for all the help and sharing of your knoldge...

--Junior Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[WildHare]

so it would go against my suporvisor to automate that field..

Tell your supervisor to polish up his/her resume. Does s/he have pointy hair (re: Dilbert)??

Allowing random users to make random decisions as to assignment of inventory control numbers means that you don't have inventory control.

Just a thought...

PS Thx for the kudos which belong to KyleS more than me.



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[Junior1544]

thanks man... i'll see if i can make it run right...

--Junior

(p.s. i hate office politics...)
Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[KyleS]

Oh come on now guys, we LOVE office politics! That's what makes our jobs interesting. "The sweet is never as sweet without the bitter" or some junk like that. I understand the situation you're in junior, we have a unique index in one of our DB's where it ("Project Name&quot comes from Finance after being approved somewhere else after being suggested here (and it never returns like it leaves) so I CAN'T assign it a number since it gets assigned a number by Finance's system (which assigns numbers to all departments the same so I can't even try to keep up with thier numbering system much less sync with it - I'm not finance so I can't touch finance) Long story short I have my own Primary Index which everything is run off and the "Project Name" which they are so big on is mostly there for thier convenience. I inherited this set-up and I'm working to fix it (long way to go) but I'm not holding my breath.

Tell you what though - this stuff will keep you on your toes... Kyle