Validation Rule

[EssKayKay]

I have a MS Access2000 database where we track real estate sales. I have two fields - [sale_company] and [list_company]. At least one of these two fields must have our office name (LTC) in it (both could have it). I want a validation rule (or something) to assure us that we didn't inadvertantly forget to enter LTC in one of the fields.

Any suggestions?
Thanks,
Steve Kipping
Lake Town & Country Real Estate

 

[Corticus]

..I have two fields - [sale_company] and [list_company]. At least one of these two fields must have our office name (LTC) in it (both could have it). I want a validation rule (or something) to assure us that we didn't inadvertantly forget to enter LTC in one of the fields.

Hi,

A few questions first...
What kind of data do the fields [sale_company] and [list_company] contain. Why would you want only one of the two to hold LTC. If one field is a field for your company name, that should have a default value of "LTC". If they hold some other company besides yours, then I would add a field to the table that contains these fields, and make the default value of that field "LTC".
If you have to check for "LTC" to be in one of those two already existing fields, I would allow form entry only to the table you need to validate. Go to the form properties and turn 'navigation buttons' off. Insert a pushbutton using the wizard, and have it select the next record. Add another button to create a new record. Now, with these two buttons you have an event to trigger your validation test.
Now I need more info. Will "LTC" be at the beginning or end of the string? Will it ever be preceded by symbols, like quotes. If its at the end, is there ever anything after it? You can test the whole string for "LTC" anywhere in it, too.
The only way I can think of would be to use the replace formula(I know there's a better way) and test the two fields like:

edited out because the code below is better,

edit:<pre>Private Sub Command0_Click()
If IsNull(Txt1) Or IsNull(Txt2) Then
MsgBox ("Enter Data in Both Fields")
Exit Sub
End If
If InStr(Txt1, "LTC") = 0 And InStr(Txt2, "LTC") = 0 Then
MsgBox ("Put LTC Somewhere, fool!")
Exit Sub
Else:
Application.DoCmd.GoToRecord , , acNewRec
End If
End Sub</pre>

HTH,
____________<EMBED src=http://mitglied.lycos.de/corticus/sig.swf width=170 height=70>
Corticus@TheOfficeExperts.com
TheOfficeExperts.com
This message was edited by corticus on 2003-01-07 16:37
This message was edited by corticus on 2003-01-13 10:30

 

[EssKayKay]

Hi,

A few questions first...

What kind of data do the fields [sale_company] and [list_company] contain.


text

Why would you want only one of the two to hold LTC

I want one or two to hold LTC but at least one.

If one field is a field for your company name, that should have a default value of "LTC". If they hold some other company besides yours, then I would add a field to the table that contains these fields, and make the default value of that field "LTC".

The fields are [list_company] and [sale_company]. One of our agents can hold the listing of the property (i.e., [list_company]); can be the sales agent of the property 9[sale_company] or both. But we have to be at least a lister or seller of the property to care about tracking it. The commission splits are determined by what agent and what company are responsible for the listing end or selling end of the transaction.

If you have to check for "LTC" to be in one of those two already existing fields, I would allow form entry only to the table you need to validate. Go to the form properties and turn 'navigation buttons' off. Insert a pushbutton using the wizard, and have it select the next record. Add another button to create a new record. Now, with these two buttons you have an event to trigger your validation test.

Now I need more info. Will "LTC" be at the beginning or end of the string?

LTC will be the data input into the field (either [list_company], [sale_company], or both). That's all that's in the field is the company name. If it is not our listing, the [list_company] would be some other company like Coldwell-Banker, or Century-21, etc.

Will it ever be preceded by symbols, like quotes. If its at the end, is there ever anything after it? You can test the whole string for "LTC" anywhere in it, too.

No

The only way I can think of would be to use the replace formula(I know there's a better way) and test the two fields like:

If Len(Replace(txt1, "LTC", "")) = Len(txt1) Then

If Len(Replace(txt2, "LTC", "")) = Len(txt2) Then

MsgBox ("Enter 'LTC' into one of the two fields")

End If

Exit Sub

End If


Not the most elegant solution, but should suffice...

edit:
okay, you can use something like:
If InStr(txt1, "LTC")and InsStr(txt2, "LTC") < 1 Then MsgBox("Put LTC Somewhere, fool!")

HTH,




See above for some of my comments on your questions.

Where do I put the code you wrote? I tried it using the Properties of the [list_company] and [sale_company] under the Event > Before Update > Code portion but received and error.

If I can't do this, no biggie. We'll just have to make sure we don't inadvertantly enter the wrong company (I'm using pull down menus for input)

Any other suggestions?

Thanks again,
SKK

 

[Corticus]

Hi!

To use the code I suggested, use the form wizard to create a form linked to the table of interest. Walk through the steps and get the thing looking nice. Make sure its in design mode, and right click on the little square button thing on the top-left corner of the form, and select properties. Turn the 'navigation buttons' property to no.
Then insert three buttons on the form, and use the wizard to make one go forward a record, one back a record, and one to create a new record. Remember the names of the textboxes that will potentially house "LTC". When click any of these new buttons, you will trigger the code that tests thes txtbox's contents.
Right click on each button, and select build event|Code Builder. You should see something like this:

<pre>Option Compare Database
______________________________
Private Sub Command0_Click()

Application.DoCmd.GoToRecord , , acNewRec

End Sub</pre>

This would be for the button to add a new record.

Add to it so it looks like this:
<pre>Private Sub Command0_Click()

If IsNull(Txt1) Or IsNull(Txt2) Then
MsgBox ("Enter Data in Both Fields")
Exit Sub
End If

If InStr(Txt1, "LTC") = 0 And InStr(Txt2, "LTC") = 0 Then
MsgBox ("Put LTC Somewhere, fool!")
Exit Sub
Else:
Application.DoCmd.GoToRecord , , acNewRec
End If
End Sub</pre>

Repeat and adjust for each button, I made this db, so letme know if you want a copy tolook at, it works.

HTH,

 

[EssKayKay]

Thanks alot. Will do.

SKK