6 cases from a textbox control that needs to be validated.

I agree with you about the list and I think I will stick with DLookup and use
the code Before Update like you suggested.

I'm just about to print it right now and I hope I can get it right?

Thanks for simplifying Dave,
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

I'm not too crazy about your list box idea. Seems it would take up a lot of
real estate on the form and users may overlook an account already used. It
just looks in the table to see if the number is used.

There is another trick you may be interested in. It is possible to auto
assign account numbers. One is to increment each number by 1, another is a
table with a list of numbers to be used. If you are interested, let me know.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Hi Dave,

Thanks for more advice. I was considering a list box that would have the
used account numbers. I haven't done this yet. I'm not sure if that is a
good idea? I was thinking that the user can see which account numbers are
used already.

I can enter your code and see if I can do this. You are one step ahead of
me because I am getting the Access message saying" you are already using this
account number and the field cannot accept duplicates, would you like to
change that feature? on the table, I presume? I'm sorry, I don't remember the
exact warning.

I'll give this code a try Dave, I'm going to have quite a few account number
records. I hope the user doesn't mind the hit and miss.

If I use a List Box on the same form, perhaps the list can get columns from
the table with a Select tbl statement as a Rowsource?

Thanks again Dave, I hope you don't mind if I post on this thread again, if
I incur a prob. Maybe you can let me know what you think about a list Box.
I'm still designing this database.

Rob Martellini
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If you want to ensure you get no duplicate account numbers, you can also use
the Before Update event for that. After the Select Case statement, you can
use a DLookup to see if the account number already exists in the table and
cancel the update if it does:

If Not IsNull(DLookup("[AccountNumber]", "TableName", "[AccountNumber] =
" _
& Me.txtAccountNumber)) Then
MsgBox "Account Number " & Me.txtAccountNumber & " Is in use", _
vbExclamation
Me.txtAccountNumber.Undo
Cancel = True
End If

You would have to use your correct field an table names, of course.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, I don't know how to thank you, but thank you so much.

IT WORKED! I tried all the various possibilities by entering wrong data and
the message box appeared and the focus went back to the Account Number
control.

I used no Dupicates in the table for Account Numbers, I hope this doen't
pose a problem. I don't think so. And I noticed that the user can change
the Account type after the message and the focus is back on Account Number.
I tried and it did work.

You really helped me out alot and I appreciate it. I wish I could be of help.

Thanks alot Dave,

Rob martellini


--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

Rather than the After Update event. use the Before Update event. It can be
canceled which will stop the field from being updated and leave the focus on
the control.

You would just add this one line:

Case "Asset"
If Me.txtAccountNumber "1000" Or Me.txtAccountNumber "1999"
Then
MsgBox "Account Number is Out of Range"
Cancel = True
End If

It is not necessary to declare a text box or any other control that is on
the form. To avoid typos in names, use the Option Explict statement at the
top of the module, just below Option Compare Database. That will cause an
error to raise if you enter a name that has not been declared.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, I'm sorry it was a typo on my part that caused the problem, but

It seems to jump to the next record after I enter the data that is out of
range, specifically after clicking on the MsgBox "Account Number is Out of
Range"

I want the focus back to the account number and the record not saved unless
the conditions are met.

Do you think you could help me with that?

Thanks so much ,

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave, I tried the code and I should have mentioned the the txtAcctType field
is locked. I don't know why I'm getting a compile errror and Me.txtAcctType
is highlighted.

I used Private Sub txtAcctNumber Before Update

I declared

Dim txtAcctType As Texbox

My AcctNumber is an Integer on the table
Ny AccountType is Text on The table.


I did use Choose Function as a Control Source from the Option Group for
"Asset", Liability, Equity etc. That seems to work and then I loked
AccountType so the user can't type anything in.

It won't save a record and I still have txtAcctType Locked.

Should their be Dim for each of the 2 controls on my vb statement. Is it
a function? IS it okay as Before update? I'm using Access 2000.

Sorry, this is my first line of code. Done alot of reading, but not any
coding.
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

First of all thank you very much for your help. I did not know you can use
an If with Select case statement.

To answer the question, I posted this question, maybe not word for word, but
I did'nt receive a reply. I thought I read in one of the Discussion gropus
that their could not be more than 3 conditions. Maybe it was in reference to
IIF. I am not sure about what the post was really about. Probably my
mistake in not understanding the nature of the question.

Thanks Dave Hargis, you really helped me out alot.

Best wishes, Robert
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

A Select Case statement would work well.

Select Case Me.txtAccountType
Case "Asset"
If Me.txtAccountNumber "1000" Or Me.txtAccountNumber "1999"
Then
MsgBox "Account Number is Out of Range"
End If
Case "Liability"
Case "Equity"
Case "Sales"
Case "Cost of Sales"
End Select

You would do the same for the other options.

I don't know what you mean by Access can't hold more than 3 conditions.
That is not a correct statement in any context I can think of except for a
TriState control which can only be True, False, or Null.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

Dave, Sorry.

I just happen to notice that when I enter an account number 12000 i.e
for Account Type "Asset" I'm not getting an "Outof Range" message.

The same thing goes for an account number 21000 i.e for Account Type
"Liability".

It seems that The first digit "1" can be a number greater 1999 for an Asset
Account Type. But if I enter 21000 for an Asset or lower than 1000, the
message will pop up.

I believe it has to do with accepting the first number or first 4 numbers.

The code is okay. (This is just the end range part)
me.txtAccountNumber "1999" Then_
MsgBox......

Seems to be okay.

I used AcctNum as my Control Source, which is the field name in the table.

I'm sorry, I don' remember if I checked large numbers that start with the
same first digit i.e 10000, 120000, 12789, It will accept these for "Asset"

210000, 23490, and so on, it will accept these for "Liability"

Anyways, I'm trying, but I can't seem to fix this.

Thanks again, only if you have time. I know I have expended alot of your
time,

Rob martellini






--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

If the values you are comparing are text,
"2" is greater than "1000"
But if you are comparing them as numbers, then
2 is lesss than 1000

If they are stored as text and you need to compare their numerical values,
you can use CLng("2") CLng("1000") Then it would return false.

--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, Sorry.

I just happen to notice that when I enter an account number 12000 i.e
for Account Type "Asset" I'm not getting an "Outof Range" message.

The same thing goes for an account number 21000 i.e for Account Type
"Liability".

It seems that The first digit "1" can be a number greater 1999 for an Asset
Account Type. But if I enter 21000 for an Asset or lower than 1000, the
message will pop up.

I believe it has to do with accepting the first number or first 4 numbers.

The code is okay. (This is just the end range part)
me.txtAccountNumber "1999" Then_
MsgBox......

Seems to be okay.

I used AcctNum as my Control Source, which is the field name in the table.

I'm sorry, I don' remember if I checked large numbers that start with the
same first digit i.e 10000, 120000, 12789, It will accept these for "Asset"

210000, 23490, and so on, it will accept these for "Liability"

Anyways, I'm trying, but I can't seem to fix this.

Thanks again, only if you have time. I know I have expended alot of your
time,

Rob martellini






--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

Dave,

The AccountNum field in my table is number, Long Integer.

The Control Source property is AccountNum in my text box named txtAccountNum.

I believe(d) it is a numeric value that is compared in the code Before Update?

At least that's the number range that I wanted a number within 1000 and 1999
for an Asset. I also noticed that the "Asset" text value is not in the table
for each record that is on the form. I don;t know if this should be the case
because, my Control Source is a Choose Function of all the option values,
"Asset", "Liability" etc.

If I change the property Control Source on (DATA) tab to AccountType then it
also changes the Control Source on (ALL)tab to AccountType and the Choose
Function disappears.

The only thing I'm wondering about is if I dragged a new textbox on to the
form from the field list, would this have changed anything.

I tried to use the same code and I'm using txtAcctNum as the textbox Control
name and The controlSource is the fieldname from the table Acctnum.

I guess I have pretty well exhausted this thread.

Thanks Dave,

Rob





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If the values you are comparing are text,
"2" is greater than "1000"
But if you are comparing them as numbers, then
2 is lesss than 1000

If they are stored as text and you need to compare their numerical values,
you can use CLng("2") CLng("1000") Then it would return false.

--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, Sorry.

I just happen to notice that when I enter an account number 12000 i.e
for Account Type "Asset" I'm not getting an "Outof Range" message.

The same thing goes for an account number 21000 i.e for Account Type
"Liability".

It seems that The first digit "1" can be a number greater 1999 for an Asset
Account Type. But if I enter 21000 for an Asset or lower than 1000, the
message will pop up.

I believe it has to do with accepting the first number or first 4 numbers.

The code is okay. (This is just the end range part)
me.txtAccountNumber "1999" Then_
MsgBox......

Seems to be okay.

I used AcctNum as my Control Source, which is the field name in the table.

I'm sorry, I don' remember if I checked large numbers that start with the
same first digit i.e 10000, 120000, 12789, It will accept these for "Asset"

210000, 23490, and so on, it will accept these for "Liability"

Anyways, I'm trying, but I can't seem to fix this.

Thanks again, only if you have time. I know I have expended alot of your
time,

Rob martellini






--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

If you have a Choose function as the control source of a text box, then it
will not update anything to the table.

I'm not sure I follow all this, but if you are combining the text value and
the numeric value to do the compare, then it will compare as text.

As to dragging another text box on to the form, I don't know that it would
intefer with anything else on the form.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave,

The AccountNum field in my table is number, Long Integer.

The Control Source property is AccountNum in my text box named txtAccountNum.

I believe(d) it is a numeric value that is compared in the code Before Update?

At least that's the number range that I wanted a number within 1000 and 1999
for an Asset. I also noticed that the "Asset" text value is not in the table
for each record that is on the form. I don;t know if this should be the case
because, my Control Source is a Choose Function of all the option values,
"Asset", "Liability" etc.

If I change the property Control Source on (DATA) tab to AccountType then it
also changes the Control Source on (ALL)tab to AccountType and the Choose
Function disappears.

The only thing I'm wondering about is if I dragged a new textbox on to the
form from the field list, would this have changed anything.

I tried to use the same code and I'm using txtAcctNum as the textbox Control
name and The controlSource is the fieldname from the table Acctnum.

I guess I have pretty well exhausted this thread.

Thanks Dave,

Rob





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If the values you are comparing are text,
"2" is greater than "1000"
But if you are comparing them as numbers, then
2 is lesss than 1000

If they are stored as text and you need to compare their numerical values,
you can use CLng("2") CLng("1000") Then it would return false.

--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, Sorry.

I just happen to notice that when I enter an account number 12000 i.e
for Account Type "Asset" I'm not getting an "Outof Range" message.

The same thing goes for an account number 21000 i.e for Account Type
"Liability".

It seems that The first digit "1" can be a number greater 1999 for an Asset
Account Type. But if I enter 21000 for an Asset or lower than 1000, the
message will pop up.

I believe it has to do with accepting the first number or first 4 numbers.

The code is okay. (This is just the end range part)
me.txtAccountNumber "1999" Then_
MsgBox......

Seems to be okay.

I used AcctNum as my Control Source, which is the field name in the table.

I'm sorry, I don' remember if I checked large numbers that start with the
same first digit i.e 10000, 120000, 12789, It will accept these for "Asset"

210000, 23490, and so on, it will accept these for "Liability"

Anyways, I'm trying, but I can't seem to fix this.

Thanks again, only if you have time. I know I have expended alot of your
time,

Rob martellini






--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

Dave,

Thanks for all your help.

I may change the form and rid the option box?

Thanks alot for your time and effort. You have gone beyond and I learn't
alot.

Rob Martellini
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If you have a Choose function as the control source of a text box, then it
will not update anything to the table.

I'm not sure I follow all this, but if you are combining the text value and
the numeric value to do the compare, then it will compare as text.

As to dragging another text box on to the form, I don't know that it would
intefer with anything else on the form.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave,

The AccountNum field in my table is number, Long Integer.

The Control Source property is AccountNum in my text box named txtAccountNum.

I believe(d) it is a numeric value that is compared in the code Before Update?

At least that's the number range that I wanted a number within 1000 and 1999
for an Asset. I also noticed that the "Asset" text value is not in the table
for each record that is on the form. I don;t know if this should be the case
because, my Control Source is a Choose Function of all the option values,
"Asset", "Liability" etc.

If I change the property Control Source on (DATA) tab to AccountType then it
also changes the Control Source on (ALL)tab to AccountType and the Choose
Function disappears.

The only thing I'm wondering about is if I dragged a new textbox on to the
form from the field list, would this have changed anything.

I tried to use the same code and I'm using txtAcctNum as the textbox Control
name and The controlSource is the fieldname from the table Acctnum.

I guess I have pretty well exhausted this thread.

Thanks Dave,

Rob





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If the values you are comparing are text,
"2" is greater than "1000"
But if you are comparing them as numbers, then
2 is lesss than 1000

If they are stored as text and you need to compare their numerical values,
you can use CLng("2") CLng("1000") Then it would return false.

--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, Sorry.

I just happen to notice that when I enter an account number 12000 i.e
for Account Type "Asset" I'm not getting an "Outof Range" message.

The same thing goes for an account number 21000 i.e for Account Type
"Liability".

It seems that The first digit "1" can be a number greater 1999 for an Asset
Account Type. But if I enter 21000 for an Asset or lower than 1000, the
message will pop up.

I believe it has to do with accepting the first number or first 4 numbers.

The code is okay. (This is just the end range part)
me.txtAccountNumber "1999" Then_
MsgBox......

Seems to be okay.

I used AcctNum as my Control Source, which is the field name in the table.

I'm sorry, I don' remember if I checked large numbers that start with the
same first digit i.e 10000, 120000, 12789, It will accept these for "Asset"

210000, 23490, and so on, it will accept these for "Liability"

Anyways, I'm trying, but I can't seem to fix this.

Thanks again, only if you have time. I know I have expended alot of your
time,

Rob martellini






--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

Hi Dave!

I changed the AcctNum field to Text and I made the size 4 characters.

This would work for now.

I tried to type an expression that would show the Account Type in the table
for each record on the table design for Chart of Accounts.

=forms! frmChartOfAccounts! [AcctType]

The error I got was could not find field.

Anyways, I will try somehow to get the Account Type data to show in the table.

Just thought I would let you know.

Take care,

Robert
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

Thanks for all your help.

I may change the form and rid the option box?

Thanks alot for your time and effort. You have gone beyond and I learn't
alot.

Rob Martellini
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If you have a Choose function as the control source of a text box, then it
will not update anything to the table.

I'm not sure I follow all this, but if you are combining the text value and
the numeric value to do the compare, then it will compare as text.

As to dragging another text box on to the form, I don't know that it would
intefer with anything else on the form.
--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave,

The AccountNum field in my table is number, Long Integer.

The Control Source property is AccountNum in my text box named txtAccountNum.

I believe(d) it is a numeric value that is compared in the code Before Update?

At least that's the number range that I wanted a number within 1000 and 1999
for an Asset. I also noticed that the "Asset" text value is not in the table
for each record that is on the form. I don;t know if this should be the case
because, my Control Source is a Choose Function of all the option values,
"Asset", "Liability" etc.

If I change the property Control Source on (DATA) tab to AccountType then it
also changes the Control Source on (ALL)tab to AccountType and the Choose
Function disappears.

The only thing I'm wondering about is if I dragged a new textbox on to the
form from the field list, would this have changed anything.

I tried to use the same code and I'm using txtAcctNum as the textbox Control
name and The controlSource is the fieldname from the table Acctnum.

I guess I have pretty well exhausted this thread.

Thanks Dave,

Rob





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Klatuu" wrote:

If the values you are comparing are text,
"2" is greater than "1000"
But if you are comparing them as numbers, then
2 is lesss than 1000

If they are stored as text and you need to compare their numerical values,
you can use CLng("2") CLng("1000") Then it would return false.

--
Dave Hargis, Microsoft Access MVP


"Rob M." wrote:

Dave, Sorry.

I just happen to notice that when I enter an account number 12000 i.e
for Account Type "Asset" I'm not getting an "Outof Range" message.

The same thing goes for an account number 21000 i.e for Account Type
"Liability".

It seems that The first digit "1" can be a number greater 1999 for an Asset
Account Type. But if I enter 21000 for an Asset or lower than 1000, the
message will pop up.

I believe it has to do with accepting the first number or first 4 numbers.

The code is okay. (This is just the end range part)
me.txtAccountNumber "1999" Then_
MsgBox......

Seems to be okay.

I used AcctNum as my Control Source, which is the field name in the table.

I'm sorry, I don' remember if I checked large numbers that start with the
same first digit i.e 10000, 120000, 12789, It will accept these for "Asset"

210000, 23490, and so on, it will accept these for "Liability"

Anyways, I'm trying, but I can't seem to fix this.

Thanks again, only if you have time. I know I have expended alot of your
time,

Rob martellini






--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Dave,

I just screwed someting up. An "asset Account will accept a value of 12000.

The Accout Type is not showing up in my table. I may have overlooked that
last night. I don't know. I did not enter the Dlookup code at all.

I think I dragged a text box down Accout Number on to the form and I tried
to set up the props I'm using AccountNum insted of txtAccountNum, I'm getting
it from the List box from the table list. I think the contol source is wrong
for both controls 1)AccountType which is the Choose function. But the data
doesn't appear in the table i.e Asset.

I feel like I screwed up something. I should get "out of range mesage for
an entry of "12000"

Take care Dave, I have to get away for awhile.

Rob
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.



"Rob M." wrote:

Hi,

I have a textbox control that is getting info from an Option Box.

The txtAccountType control has values such as Asset. Liability. Equity.
Sales and Cost of Sales.

What I need is the code and if it is a function or not? Also I was wondering
if it would be a Select Case code Before update? I'm not sure?

Specifically, if the txtAccountType = "Asset" Then
_
txtAccountNumber would have to be between "1000 and 1999"

This would be 1 of the account ranges for the 6 Account Type in The
txtAcctType control.

If txtAccountType="Liability" Then

txtAccountNum= Between 2000 and 2999.

I have read somehwere on one of the dicussion groups that Acces can't hald
more than3 condtions. I'm not sure about this.

Thanks for your help.





--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.