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 said:
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
:
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.
:
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
:
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.
:
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.
:
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.
:
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
:
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.