Link to home
Start Free TrialLog in
Avatar of shaz0503
shaz0503

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Philippe Damerval
Philippe Damerval
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shaz0503
shaz0503

ASKER

damerval

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


rgds
My mistake (got mixed up with javascript for a second :) you are looking for the FORM events called Before Update and Before Insert.
And I'm sorry, the cancel line should read :
cancel = true
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


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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
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
Hi,

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

Bill
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
...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....
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Shaz,

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

Thanks,

Bill
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