For.. Next Statement

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hi,

I have a query with 2 numbers in each record, being a High Number & a Low
Number allocated to each client

I want to put in a form an AfterUpdate module that checks the entered number
does not fall between the numbers in the query.

Eg data in the query could be:

Low High
401 600
901 1000
1001 1100

so if someone entered on my form 520, I could display a message indicating
the number has alreay beed used as it falls between 401 & 600. BUT if
someone entered 601 it would not display a message.

I beleive I can do this using the For..Next statement, but I need a little
help with it, as my VBA knowledge is limited

Could someone direct me to a website that may explain how I could do this a
little better that the Help files do.

Thanks in advance,
Anthony.
 
No need for a loop. You can do it with a DLookup().

This example assumes:
- a table named "MyTable";
- Number-type fields named "ID" (primary key), "Low", and "High";
- a text box named "txt1" where the user enters the number.

Dim strWhere As String
Dim varResult As Variant
If Not IsNull(Me.[txt1]) Then
strWhere = Me.[txt1] & " Between [Low] And [High]"
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
MsgBox "Clashes with ID " & varResult
End If
End If

If you need further help with DLookup(), see:
http://members.iinet.net.au/~allenbrowne/casu-07.html
 
Thank you for that it worked great.

Just one more thing I had the query filtering, [Type] = "Cheque Book"

Now it it looking at the table this is not happening

I am having trouble adding this string to:
Something like(I know this is wrong):

strWhere = Me.[HighNumber] & " Between [LowNumber] And [HighNumber]
AND [Type] & " = 'Cheque Book' "

Thanks again for your help.

Anthony.


Allen Browne said:
No need for a loop. You can do it with a DLookup().

This example assumes:
- a table named "MyTable";
- Number-type fields named "ID" (primary key), "Low", and "High";
- a text box named "txt1" where the user enters the number.

Dim strWhere As String
Dim varResult As Variant
If Not IsNull(Me.[txt1]) Then
strWhere = Me.[txt1] & " Between [Low] And [High]"
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
MsgBox "Clashes with ID " & varResult
End If
End If

If you need further help with DLookup(), see:
http://members.iinet.net.au/~allenbrowne/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Anthony said:
Hi,

I have a query with 2 numbers in each record, being a High Number & a Low
Number allocated to each client

I want to put in a form an AfterUpdate module that checks the entered
number does not fall between the numbers in the query.

Eg data in the query could be:

Low High
401 600
901 1000
1001 1100

so if someone entered on my form 520, I could display a message
indicating the number has alreay beed used as it falls between 401 & 600.
BUT if someone entered 601 it would not display a message.

I beleive I can do this using the For..Next statement, but I need a
little help with it, as my VBA knowledge is limited

Could someone direct me to a website that may explain how I could do this
a little better that the Help files do.

Thanks in advance,
Anthony.
Click to expand...
Click to expand...
 
If the Type field is a Text field, then you need to enclose it in quotes,
i.e.:
strWhere = "(" & Me.[HighNumber] & " Between [LowNumber] And
[HighNumber]) AND ([Type] & ""Cheque Book"")"

If it is a Number type field with one of those nasty lookup-wizard thingies
that makes it show what's not there, you will have to supply the correct
number instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Anthony said:
Thank you for that it worked great.

Just one more thing I had the query filtering, [Type] = "Cheque Book"

Now it it looking at the table this is not happening

I am having trouble adding this string to:
Something like(I know this is wrong):

strWhere = Me.[HighNumber] & " Between [LowNumber] And [HighNumber]
AND [Type] & " = 'Cheque Book' "

Thanks again for your help.

Anthony.


Allen Browne said:
No need for a loop. You can do it with a DLookup().

This example assumes:
- a table named "MyTable";
- Number-type fields named "ID" (primary key), "Low", and "High";
- a text box named "txt1" where the user enters the number.

Dim strWhere As String
Dim varResult As Variant
If Not IsNull(Me.[txt1]) Then
strWhere = Me.[txt1] & " Between [Low] And [High]"
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
MsgBox "Clashes with ID " & varResult
End If
End If

If you need further help with DLookup(), see:
http://members.iinet.net.au/~allenbrowne/casu-07.html


Anthony said:
Hi,

I have a query with 2 numbers in each record, being a High Number & a
Low Number allocated to each client

I want to put in a form an AfterUpdate module that checks the entered
number does not fall between the numbers in the query.

Eg data in the query could be:

Low High
401 600
901 1000
1001 1100

so if someone entered on my form 520, I could display a message
indicating the number has alreay beed used as it falls between 401 &
600. BUT if someone entered 601 it would not display a message.

I beleive I can do this using the For..Next statement, but I need a
little help with it, as my VBA knowledge is limited

Could someone direct me to a website that may explain how I could do
this a little better that the Help files do.

Thanks in advance,
Anthony.
Click to expand...
Click to expand...
Click to expand...
 
Pardon me, I think Mr. Browne missed the comparison operator in his correction

strWhere = Me.[HighNumber] & " Between [LowNumber] And [HighNumber] AND [Type]
= ""Cheque Book"""

Allen said:
If the Type field is a Text field, then you need to enclose it in quotes,
i.e.:
strWhere = "(" & Me.[HighNumber] & " Between [LowNumber] And
[HighNumber]) AND ([Type] & ""Cheque Book"")"

If it is a Number type field with one of those nasty lookup-wizard thingies
that makes it show what's not there, you will have to supply the correct
number instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Anthony said:
Thank you for that it worked great.

Just one more thing I had the query filtering, [Type] = "Cheque Book"

Now it it looking at the table this is not happening

I am having trouble adding this string to:
Something like(I know this is wrong):

strWhere = Me.[HighNumber] & " Between [LowNumber] And [HighNumber]
AND [Type] & " = 'Cheque Book' "

Thanks again for your help.

Anthony.


Allen Browne said:
No need for a loop. You can do it with a DLookup().

This example assumes:
- a table named "MyTable";
- Number-type fields named "ID" (primary key), "Low", and "High";
- a text box named "txt1" where the user enters the number.

Dim strWhere As String
Dim varResult As Variant
If Not IsNull(Me.[txt1]) Then
strWhere = Me.[txt1] & " Between [Low] And [High]"
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
MsgBox "Clashes with ID " & varResult
End If
End If

If you need further help with DLookup(), see:
http://members.iinet.net.au/~allenbrowne/casu-07.html


Hi,

I have a query with 2 numbers in each record, being a High Number & a
Low Number allocated to each client

I want to put in a form an AfterUpdate module that checks the entered
number does not fall between the numbers in the query.

Eg data in the query could be:

Low High
401 600
901 1000
1001 1100

so if someone entered on my form 520, I could display a message
indicating the number has alreay beed used as it falls between 401 &
600. BUT if someone entered 601 it would not display a message.

I beleive I can do this using the For..Next statement, but I need a
little help with it, as my VBA knowledge is limited

Could someone direct me to a website that may explain how I could do
this a little better that the Help files do.

Thanks in advance,
Anthony.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top