pop up notice from searching table database

[cikwan82]

Hi every one, i already have many table for each month , JanuaryT-DisemberT. I'm using simple form to key in data inside that table. Inside to that table (JanuaryT-DisemberT) have project number data i want to notice pop up when i try key in if that project number not valid..

example

JanuariT
Name -Project Cod- Validation
John -2012ABC - Not valid
Michel-2013WER-Valid
Mike-2014TERG-Valid

FebruariT
Name -Project Cod- Validation
Ann -2011AAA - Not valid
Sam-2012BCB-Valid
Tony-2013TEC-Valid



Q: What i want to do is when i want to key in data in MacT using Form with ADD butoon, some notice will come out if .... for example : Daniel-2011AAA or Jony-2012ABC the notice will come out " THIS PROJECT COD NOT VALID, PLEASE CHOOSE ANOTHER COD". it something like notice to tell me that project cod already not valid yet. It look like "automatic searching" data for every table. It Is possible to do like this?

 

[CJ_London]

anything is possible - but you have not said how you decide whether a project is valid. For example, do you have another table with project data and the code is valid if the code exists in the project table and not valid if it doesn't exist?

 

[cikwan82]

it look like notice to tell me that cod already use by another person in previous month and not valid yet. If valid, that data will go inside to my table MacT. if possible can give me guide how to write that sql coding in my ADD button. Thank you

 

[CJ_London]

in your form project cod afterupdate event put the following - you'll need to change field and table names to the ones you use

if not isNull dlookup("[Project Cod]","myTable","Project Cod='" & me.[project Code] & "'") then
msgbox "This project cod already exists",vbokonly
me.[project code]=""
end if

However I think you are saying you have many tables, one for each month. This is a bad design, you should have one table and include a field for the month. If there is a good reason for having so many tables then you will need to repeat the above code for each table

 

[cikwan82]

ok.. i already make just one table (query table) for all month and just try this sql to my INSERT button form

where i change
Project Cod= NoGerankod
myTable=HutangKeseluruhan
Me.project cod= Me.txtnogeran

If Not IsNull(DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then
MsgBox "This project cod already exists", vbOKOnly
Me.[txtnogeran] = ""

End If

But the data insert in without come out any msg "This project cod already exists" when i try to put the project cod number had state in my query table (HutangKeseluruhan). Any correction must i do?

P/S: HutangKeseluruhan= List of all my code number not valid

 

[CJ_London]

I would use debugging to check the values

so put

Debug.Print DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")
Debug.Print Me.txtnogeran

just before your if statement and look in the immediate window for the values it produces

Also, I did say to put the code in your form project cod afterupdate event which now you have provided values should be the txtnogeran control afterupdate event

 

[cikwan82]

ok, i got it.. Thank you CJ_London...