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.

 

[KyleS]

OK Junior,

A little more info would help. From what we have here, I would just use a DAO.Recordset.

something like this:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourQUERYNAME&quot
rst.Index = "YOURINDEXNAME"
rst.Seek "=", Forms!YOURFORMNAME!YOURFIELDNAME

If rst.NoMatch Then
'Data is valid
Else
'Data isn't valid
End If

rst.Close
Set rst = Nothing Kyle :

 

[Junior1544]

ok, Thank you. But i'm not sure what a recordset is... is that some thing i have to define, or is it another name of some thing i would probably already have...

I already have a Query that displays if the data is present, is that the recordset?? i'm just a little confused...

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

 

[KyleS]

OK, well above is just some code that call up a recordset (which is just the query in a way that access can look through it), the recordset is just a copy of your query which is why you would put your query name in the "OpenRecordset" line.

So if it's there (the data is not valid) then the query comes up with one line and if there's not match (the data is valid) then the query will bring up that record. So the query is Null(Good) or not null(Bad). Is this how you've got it set up?

Go into the design view of your form, right click on the field we are checking and go to properties. Got to the "Events" tab and select "OnLostFocus" then click th button on the far right of the field with three dots ie "..."

Then paste this in the code page it brings up:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourQUERYNAME&quot

If rst.Recordcount = 0 Then
'Data is valid
Else
'Data isn't valid
End If

rst.Close
Set rst = Nothing

This is the code to use if your query works just like I stated in this post. If your query brings up a large list then use the code I posted first.

The first code goes though a large list and looks for your field, and let's you know if it is in there, the second set of code will look at a query and tell you if there are any records.

On a side note, if this field always has to be unique, you could just set it as a Primary Key - or just index it allowing no duplicates. That way Access would check for you.

Kyle :

 

[Junior1544]

ok, I'm using the top set of code, it all looks good from my limited knoldge of basic... but i keep getting an error...

Run-Time error '3251':
Operation is not supported by Object.
what's this mean?? it then gives me the option to debug... if it helps i'm using office 2k...

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

 

[KyleS]

What line does it highlight?

since you're using office 2K, make sure under References you have Microsoft DAO 3.6 Objects selected.

to do this go to the code view, Tools-->References and then you can check. It defaults under A '97 but under A 2000 you have to select it.

Let me know.
Kyle :

 

[Junior1544]

rst.Index = "YOURINDEXNAME"
is the line... from the top set of code...

and the dao 3.6 reference is in there...

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

 

[KyleS]

Go into the design view of your table and go to View-->Indexes, you need to put one of those IndexNames where "YOURINDEXNAME" is. I just tried this using DAO 3.6 and it worked fine...

Using this code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblprojects&quot
rst.Index = "PrimaryKey"
rst.Seek "=", "45"
MsgBox rst!ID
rst.Close
Set rst = Nothing

I don't know why you're getting the "Operation is not suppoerted by this object" line. I don't get that. It's not supported by ADO recordsets but we've dimensioned this as a DAO recordset. Did you copy the code exactly (If you left off the "DAO" that would be the problem) Go back into your references and move the DAO reference above the ADO reference using the arrows to the right of the list box. Kyle :

 

[Junior1544]

i'm still confused... it's still giving me the same errors... i changed the index to a valid field name that is indexed within that table... and it's still giving me the error... I also moved the DAO reference as far up as it would let me...

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

 

[KyleS]

That doesn't make any sense...Do me a favor, paste you code here for me (there's probably nothing wrong with it, I'm just stumped - I'm using the same DAO (3.6) as you in my tests and it's going fine) Kyle :

 

[KyleS]

OK, I just thought of something, you're pulling this off a query so using an index that's in the table is the problem, I haven't used DAO so I'll go ahead and get a test working and post it for you OK? I'll just need a minute... Kyle :

 

[Junior1544]

i was just thinking about that... i wish i knew more about vb... thank you so much for your hard work... i just wish i could repay it some how...

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

 

[Junior1544]

Private Sub VNUM_LostFocus()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("BDG&quot'the query i'm testing against
rst.Index = "VNUM"'the field that is indexed
rst.Seek "=", "Forms!VISITOR!VNUM"'the form and field in the form i'm testing.

If rst.NoMatch Then
MsgBox ("good&quot'just till i get this working, then i will add stuff in here...
Else
MsgBox ("bad&quot
End If

rst.Close
Set rst = Nothing
End Sub


this is the exact code i'm using... i've commented a little more so you will know what i'm thinking... Junior1544@yahoo.com
Life is change. To deny change is to deny life.

 

[KyleS]

OK, here you go, sorry about that.

Private Sub VNUM_LostFocus()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("BDG&quot'the query i'm testing against
rst.FindFirst "VNUM = " & Forms!VISITOR!VNUM

If rst.NoMatch Then
MsgBox ("good&quot'just till i get this working, then i will add stuff in here...
Else
MsgBox ("bad&quot
End If

rst.Close
Set rst = Nothing
End Sub Kyle :

 

[Junior1544]

ok, i'm not getting the other error any more, now i'm getting a missing operator...

but i am less confused studding this, and reading code elsewhere in the db... i'm starting to understand the logic...

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

 

[Junior1544]

it's now telling me that the data i enter isn't a valid field name. I wish i had a working model within my database to work off of.... thanks for all the time you've put into this...

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

 

[KyleS]

VNUM is the name of the field in thw query we're looking at, right?

And Forms!VISITOR!VNUM is the field on the form, right?

And it's a number, and formatted as a number in the table, right? Kyle :

 

[Junior1544]

it's not a number, it's a text field... it has both text and numbers... so it needs to be treated as text...

i happend to see a friend of mine and she took a look at the code and made it work for me... i'll post what she did to it tomorow as soon as i get back to work... thanks for all the work... i never would have gotten any where without you...

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

 

[KyleS]

Happt to help Junior, post the code when you get a chance! Kyle :

 

[KyleS]

Happy to help Junior, post the code when you get a chance! Kyle :