code to alert that if field a has text then field b must also have text

shaz0503
shaz0503 used Ask the Experts™
on
All

Playing with a new Db and need something similar to the code for cascading combo boxes.

Field 1 [Destination country 1]
Field 2 [Country 1]
Field 3 [Destination City]

Where there is data entered in Field 1 then there must also be data entered to fields 2 and 3...

How do I enusre a user enters data in fields 2 and 3  and get a message box to prompt...

All fields are free text

rgds
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Analyst Programmer
Commented:
Hi,
In the OnUpdate and OnInsert events you would add something like this:

if field1.value <> "" then
  if (field2.value = "") or (field3.value = "") then
     msgbox "You must enter a value in field 2 and field 3 if field1 is not null"
     cancel
  end if
end if

Author

Commented:
damerval

I don't appear to have OnUpdate nor OnInsert events....am using MSO 2007 if that helps


rgds
Philippe DamervalSenior Analyst Programmer

Commented:
My mistake (got mixed up with javascript for a second :) you are looking for the FORM events called Before Update and Before Insert.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Philippe DamervalSenior Analyst Programmer

Commented:
And I'm sorry, the cancel line should read :
cancel = true

Author

Commented:
damerval

I have added the below code to both the Form Properties Before Update and Before Insert events.....nothing is happening.  Have i got this wrong...

Private Sub Form_BeforeInsert(Cancel As Integer)
 If [Destination Country 1].Value <> "" Then
        If ([Country 1 Category].Value = "") Or ([Destination City 1].Value = "") Then
            MsgBox "you must enter information in the Country; Category and City fields"
        Cancel = True
    End If
End If
End Sub

rgds


Bill RossProgrammer
Commented:
Hi Shaz,

Give this a try:

Private Sub Form_BeforeInsert(Cancel As Integer)
 If Not IsNull(Me![Destination Country 1]) Then
        If IsNull(Me![Country 1 Category]) Or IsNull(Me![Destination City 1]) Then
            MsgBox "You must enter information in the Category and City fields"
           Cancel = True
    End If
End If
End Sub
I think I would be inclined to use..
  If nz(([Country 1 Category],"")= "" Or nz([Destination City 1],"")= "" Then


Just as a comment, you will find life with Access much easier if you don't put spaces in any object names.

Author

Commented:
Bill and Peter

I had a play around yesterday and came up with the below - after borrowing some code...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[DestinationCountry1]) Then
        MsgBox "You must enter a Destination Country"
        Me.[DestinationCountry1].SetFocus
Else
    If IsNull(Me.[Country1Category]) Then
        MsgBox "You must enter Category"
        Me.[Country1Category].SetFocus
Else
    If IsNull(Me.[DestinationCity1]) Then
        MsgBox "You must enter a Destination City"
        Me.[DestinationCity1].SetFocus
Cancel = True

End If
End If
End If


End Sub


This works fine but I tried your code and nothing appears to have happened....

I am finally getting my head around this 'code thing' and wonder why one works and one doesn't....

Peter, I will endeavour to keep Db tidier....

rgds

Author

Commented:
All

In addition to the above - but similar question...

I have had several attempts at some code to:  If [Destination Country 1] is not = Australia then [DFAT Reg Number] must be entered

I have tried this and something is obviously 'missing'

Private Sub DFATRegNumber_BeforeUpdate(Cancel As Integer)
    If (Me.[Destination Country 1]) <> "Australia" And (Me.[DFAT Reg Number]) = "" Then
   
        MsgBox "You must enter a DFAT Registration number"
       
  Cancel = True

End If

End Sub
Bill RossProgrammer
Commented:
Hi,

Private Sub DFATRegNumber_BeforeUpdate(Cancel As Integer)
    If Me![Destination Country 1] <> "Australia" And IsNull(Me![DFAT Reg Number]) Then
       MsgBox "You must enter a DFAT Registration number"
       Cancel = True
  End If
end sub


Will work.  Make sure the form field names are "Destination Country 1" and "DFAT Reg Number".  I reall don't like spaces in object names either.
Bill RossProgrammer

Commented:
Hi - again,

oops - it is in the wrong place.  You should not have it at the field level. but rather at the form level.  You will never get this to work at the field level because the BeforeUpdate of a field implies that the field has ddata so it cannot be null unless it had data before....

Private Sub YourFormName_BeforeUpdate(Cancel As Integer)
    If Me![Destination Country 1] <> "Australia" And IsNull(Me![DFAT Reg Number]) Then
       MsgBox "You must enter a DFAT Registration number"
       Cancel = True
  End If
end sub

Author

Commented:
Thanks Bill

I will try this - am having 'fun' this morning - appear to have broken something so while sorting that will rename all object names so no spaces....

rgds

Author

Commented:
All

Help...

I can't seem to determine what I have done to the Db....should be able to add a new record that will be blank and when entering an Employee ID, Name and Dept Desc should appear.......

Now I get an error saying 'Can't go to the specific record'...

I don't recall changing anything except removing spaces from object names selecting relevant control source......

Any assistance appreciated

rgds
Travel1.mdb
Bill RossProgrammer

Commented:
Hi,

You need a primary key on the field Employee_ID in the table EmployeeData.  Without that your form recordsource is not updateable.

Bill

Author

Commented:
Thnaks Bill

Silly me....added the Primary key...

but why can I not get the fields to 'auto populate' for Surname; Given Name and Dept Desc.....

I could yesterday and now have done something to undo this....

rgds

Author

Commented:
...and I need to be able to have more thatn one record per employee...this is why I must have never had the primary key....
Bill RossProgrammer
Commented:
Hi Shaz,

What is your question?  The database design needs to be reviewed.  There are no relationships?  You need to set a good db design as a first step.  Once that is done then everything else will come easier.  EE is designed as a one question/one answer forum not as an ongoing db design forum to provide consulting.

That said you need to set a one-to-many relationship between EmployeeData and tblStatementDetails.  Once this is set the autopopulation can happen and you can have multiple statementdetail records per employee.

Regards,

Bill
Bill RossProgrammer

Commented:
Hi Shaz,

If you still need help please post additional questions and I'll be glad to help.

Thanks,

Bill

Author

Commented:
Thanks Bill

My problem is that I am 'attempting to develop several Db on the fly' as business needs require data outputs not avail from Enterprise systems.  

I have limited knowledge, and am learning as I go...albeit would be nice to have time to spend investing in the 'better' way of doing things..

Thank you all for you assistance and feedback on the design or lack there of.. I am learning lots from you all..

rgds

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial