Check Function

  • Thread starter Thread starter cokparae
  • Start date Start date
C

cokparae

I am a beginner in Access!

I have created a form where i will be able to type in a value. When I press
enter, I want that value to check against another query and return any
matches associated with the original value. This function needs to be a
iterative loop that will continute to check itself against the other query
until it finds a match and if no match is found, I would like an error
message to pop up if the value is not the the query or if there is a null.

I have been working at this for a couple of days now, so any help would be
appreciated.

Thanks

C
 
Are you trying to type in a value and find all the records that match? If
so, you can include a subform on your form and filter the results.

The Northwind database that ships with Access has at least one example of
this. Open Northwind and click the "Orders" button. You can then select a
customer from the drop-down, and a list of their orders are displayed. You
could do the same thing with a textbox where you type in a value or a
string.
 
Sorry - I just notice that that form actually does not list all orders for
the given supplier.

I thought there was an example in Northwind of this type of form.

Basically, you want a form with a subform that shows the results. You want
a text field at the top of the form where you enter the criteria. You want
code to run after update of that field to requery the subform.
 
I am a beginner in Access!

I have created a form where i will be able to type in a value. When I press
enter, I want that value to check against another query and return any
matches associated with the original value. This function needs to be a
iterative loop that will continute to check itself against the other query
until it finds a match and if no match is found, I would like an error
message to pop up if the value is not the the query or if there is a null.

I have been working at this for a couple of days now, so any help would be
appreciated.

Thanks

C
Click to expand...

Sorry... but you're WAY off track with regard to how queries work. No
"looping" or iteration is necessary or appropriate.

Instead, use a Parameter Query. Put a criterion on your query - either using
the query grid, on the Criteria line, or in the SQL view of the query; this
criterion would be like

=[Forms]![NameOfYourForm]![NameOfYourControl]

The query will search the database and return all of the matching records. You
can base a Form on the query, and (if you wish) open the data display form
from a command button on the form where you enter your criteria; you can have
code in the second form's Open event to give a message if there are no
records:

Private Sub Form_Open(Cancel as Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "No records found!"
Cancel = True
End If
End Sub


John W. Vinson [MVP]
 
Thanks Guys for your responses, but that’s not what I was trying to do. I
already figured out how to use a form to input a value (IP Address), press
the command button and retrieve all the matches associated with that value in
a query, form, and/or report output.

What I am trying to accomplish is using that same form to enter an IP Address
(value #1) and that address checking itself against another field (value #2)
in the same query. I am dealing with IP addresses so I assumed it is a Long
Integer. If the first three octets of value #1 match the first three octets
of the value #2 in the same query, return value 1 &2, if not decrease by 1
until there is a match. If there is no record of value 1, return “No Recordâ€.
I am not sure if I have to use VBscript on Expression builder. I have tried
both and have not be as successful as I would of like to.

I hope I explained that better.

C

I am a beginner in Access!
Click to expand...
[quoted text clipped - 11 lines]
C
Click to expand...

Sorry... but you're WAY off track with regard to how queries work. No
"looping" or iteration is necessary or appropriate.

Instead, use a Parameter Query. Put a criterion on your query - either using
the query grid, on the Criteria line, or in the SQL view of the query; this
criterion would be like

=[Forms]![NameOfYourForm]![NameOfYourControl]

The query will search the database and return all of the matching records. You
can base a Form on the query, and (if you wish) open the data display form
from a command button on the form where you enter your criteria; you can have
code in the second form's Open event to give a message if there are no
records:

Private Sub Form_Open(Cancel as Integer)
If Me.RecordsetClone.Recordcount = 0 Then
MsgBox "No records found!"
Cancel = True
End If
End Sub

John W. Vinson [MVP]
Click to expand...
 
What I am trying to accomplish is using that same form to enter an IP Address
(value #1) and that address checking itself against another field (value #2)
in the same query. I am dealing with IP addresses so I assumed it is a Long
Integer. If the first three octets of value #1 match the first three octets
of the value #2 in the same query, return value 1 &2, if not decrease by 1
until there is a match. If there is no record of value 1, return “No Record”.
I am not sure if I have to use VBscript on Expression builder. I have tried
both and have not be as successful as I would of like to.
Click to expand...

I'd still say that no iteration is needed or appropriate. Access won't, by
itself, know anything about octets; a Long Integer is the whole 32-bit Long
and will not parse out into 194.128.0.1 or anything like that! How are you
actually storing the IP address in your table, and how are you entering the
search criterion?

ASSUMING that you have four integer fields IP1, IP2, IP3 and IP4, you could
use a query searching for an exact match on IP1, IP2, and IP3; for IP4, use a
Subquery to find the largest value less than or equal to the criterion.
Without knowing your table structure I can't really suggest a query though!

John W. Vinson [MVP]
 
The IP addresses are stored as regular 32 bits (255.255.255.0). When I enter
the IP into the formss criterion, I enter it as a 32 bit as well.
What I am trying to accomplish is using that same form to enter an IP Address
(value #1) and that address checking itself against another field (value #2)
Click to expand...
[quoted text clipped - 4 lines]
I am not sure if I have to use VBscript on Expression builder. I have tried
both and have not be as successful as I would of like to.
Click to expand...

I'd still say that no iteration is needed or appropriate. Access won't, by
itself, know anything about octets; a Long Integer is the whole 32-bit Long
and will not parse out into 194.128.0.1 or anything like that! How are you
actually storing the IP address in your table, and how are you entering the
search criterion?

ASSUMING that you have four integer fields IP1, IP2, IP3 and IP4, you could
use a query searching for an exact match on IP1, IP2, and IP3; for IP4, use a
Subquery to find the largest value less than or equal to the criterion.
Without knowing your table structure I can't really suggest a query though!

John W. Vinson [MVP]
Click to expand...
 
The IP addresses are stored as regular 32 bits (255.255.255.0).
Click to expand...

I'm sorry. No, THEY ARE NOT.

The two formats you describe are contradictory. 255.255.255.0 is a Text
String, not any sort of number. Access does *not* provide a format which will
display 4294967040 in that way.

What is the Datatype of the field in your table? Is it Text? or is it Long
Integer? or is it four integers?

John W. Vinson [MVP]
 
Back
Top