Solved Update field and Validation criteria

[lacampeona]

Hello experts

I have some problems.

I have a form with several combo boxes and text boxes. I am using validation rule for saving the records.

I have a problem with one combobox who is updating the status of the status field. ( user can choose 5 different statuses). Here I am using the UPDATE statement.

When user wants to save the record and all the fields are filled correctly everythink is perfect.

But when user want to make me problems and lets say he only choose a combobox named Status and nothing else and user close the form then the field status is automaticly updated. The validation rule is working perfectly and user get a message if he want to save the record or not.

Users says no to saving the record and record is not saved but the field status is alredy updated ( becouse the combobox status has a UPDATE statment) and status is changed.What happens is that in the table I do not have record saved ( that is what I want) but the status is changed ( I dont want that becouse he did not fill all the fields).

How to prevent that user choose the status field before all the fields are entered?
I try sometnhing with combobox enabled and disabled butif I use that the problems stay. I need to prevent the update statement.

Here is my thinking what can user do:
1. User open the form and lets say he make a mistake and choose the combobox status and then close the form without saving.( He say no, I do not want to save the record-validation rule working ok), but the record is already updated becouse I have a UPDATE code in that combobox?

How to prevent that? I dont want to status be changed before all the field are filled.
Or where I have to put the update statment so the combobox will not update data before all the fields are filled?
How to prevent update data before all the fields are entered? hmmm

What is yours suggestion? What is the correct way to do that?

Thanks is advance

For Updating data in the field status I am using the next code.

If Me.StatusKoloneStanje = "(2) VUporabi" Then
'Me.AnalizaUstreza = "N/A"
DoCmd.RunSQL "UPDATE tblKolone2 SET tblKolone2.StatusKolone= '(2) VUporabi' WHERE tblKolone2.ID = " & Me.ID
'DoCmd.RunSQL "UPDATE tblKolone2 SET tblKolone2.AnalizaUstreza= 'N/A' WHERE tblKolone2.ID = " & Me.ID
DoCmd.SetWarnings (True)
End If

I try also to combine validation rule but I dont have success...hmm

If ValidateForm(Me, "required") = False Then
Cancel = True
Else
DoCmd.OpenForm "frmKoloneUporabaSignOffSub"
End If

 

[moke123]

Why use an update query?
Why not bind the combo to the field?

You should use the forms before update event for validation

 

[lacampeona]

Hello

Yes I have the before update event for validation in the form. And this is working perfectly.

I have to use the update query becouse the status is important in two different tables.

In one table the status is ok...and for status to be updated in the second table i am using update statment?
Do you think is possible to do that is some anothery way without update?

 

[Auntiejack56]

Wild Suggestion: Take the update statement out of the form, and put into the caller routine.

Let's say the form is bound to the table tblKolone1. The calling code would look something like this aircode:

myStatus = dlookup("StatusKoloneStanje","tblKolone1",,,"ID = " & myID)

docmd.Openform "myForm",,,"ID = " & myID, acDialog

' When the form closes, check to see if the Status was updated.

If Dlookup("StatusKoloneStanje","tblKolone1",,"ID = " & myID) <> myStatus then

    Status1 = Dlookup("Status1","tblKolone1",,"ID = " & myID)

    Status2 = Dlookup("Status2","tblKolone1",,"ID = " & myID)

    DoCmd.RunSQL "UPDATE tblKolone2 SET tblKolone2.StatusKolone= '" & Status1 & "' WHERE tblKolone2.ID = " & myID

    DoCmd.RunSQL "UPDATE tblKolone2 SET tblKolone2.AnalizaUstreza= '" & Status2 & "' WHERE tblKolone2.ID = " & myID

End If

 

[moke123]

But when user want to make me problems and lets say he only choose a combobox named Status and nothing else and user close the form then the field status is automaticly updated. The validation rule is working perfectly and user get a message if he want to save the record or not.

Users says no to saving the record and record is not saved but the field status is alredy updated ( becouse the combobox status has a UPDATE statment) and status is changed.What happens is that in the table I do not have record saved ( that is what I want) but the status is changed ( I dont want that becouse he did not fill all the fields).

It sounds to me that you have the update routine in the after update event of the combobox. You need to have it after the form validation and the user selecting yes to save.

 

[lacampeona]

Hi
hmmm ok I will try to implement yours suggestions

 

[lacampeona]

I have the update routine on the click event on the combobox? you mean is better to move it on before update?

 

[cheekybuddha]

It sounds more like a design problem. There should only be one place that stores the status, and you should refer to that value from the other table via a query.

But without more details on your tables setup it's impossible to advise.

 

[lacampeona]

I have two tables.
One table lets say tbl1 saves the items and items statuses and then i have tbl2 where user make a record and lets say he borrows the item and when he save the record the status changed in automaticly in the tbl2 and to have update also the tbl1 I make that update query for updating also in the tbl1.

hmmmm

 

[cheekybuddha]

Can you post a screenshot of your relationships diagram?

 

[lacampeona]

Yes
But for now I dont have no relationship yet?

 

[cheekybuddha]

You may have to translate a bit for us!

 

[lacampeona]

Hello
yes I know is very very special database that I am developing.
I will need some time and I will translate it and I will make some copy of db ( I need to delete a lot objects and left only these just to show where I have problems)

 

[cheekybuddha]

OK, you may need to describe the purpose as well.

Something to try quickly:

SELECT
  k.ID,
  k.DatumVnosa,
  k.StaraOznakaKolone,
  uk.status
FROM tblKolone2 k
INNER JOIN tblUporabaKolon uk
        ON k.KolonaID = uk.KolonaID
;

 

[lacampeona]

You mean you want me to translate you these fields? or where I have to put this code?

 

[cheekybuddha]

Create a new query.

Switch to SQL view.

Pate the code in.

Run the query.

(Also, translate all the fields so we can try and understand what is going on!)

 

[lacampeona]

OK I make the query

Ok I will explain the fields

table
tblKolone2




table
tblUporabaKolone

 

[lacampeona]

tblKolone2

ID ID
KolonaID Special ID automaticly created by vba code
StaraOznakaKolone Old item ID
DatumPrispetja Date arriving the item into the laboratory
DatumUnicenja Date of destroying the item
NazivKolone Name of the item
Proizvajalec Manufacturer
KataloskaStevilka Catalog number
LotNo Lot No
SerijskaStevilka SerialNumber
Dolzina_mm Lenght_mm
Premer_mm Diameter_mm
VelikostDelcev_um ParticleSize_mm
status status of the entry ( entered or approved)
statusnotification
userEntry
userEntryTime
userApprovalDate
userApprovalTime
Opombe Remarks
StatusKolone Status of the item ( my status problem)
SistemKolone On which system probgram will be used the item
Updates
DataModified
UpdatedBy

tlbUporabaKolon
PrevzemKID Usage ID
ID
KolonaID Special ID automaticly created by vba code
StaraOznakaKolone Old item ID
DatumVnosa Date of entry ( i make default to now)
DatumUporabe Date of usage ( when the analysis was done in laboratory)
OddelekIzvajalec Department who is doing the analysis
NamenUporabe Purpose of the usage ( Testing, changing status, relasing, NormalAnalysis)
SproscanjeUstrezno RelasePassed
AnalitskiPostopek1 AnalyticalProcedure1
AnalitskiPostopek2 AnalyticalProcedure2
OznakaInstrumenta Name of the instrument
EmpowerMapa EmpowerFolder
RefNaLabDnevnik Reference to laboratoy notebook
StranLabDnevnik Page of laboratoy notebook
AnalizaUstreza AnalysisCorrect
StatusKolone Status of the item ( my status problem)
OpombeIzvajalec Remarks who make the analysis
OpombePregledovalec Remarks who approved the analysis
Updates
Analiza1 Analysis1

 

[lacampeona]

 

[lacampeona]

Name of the statuses:
(1) OnStorage
(2) InUse
(3) Relased
(4) NotOK
(5) Testing
(6) Sending
(7) ForAnotherPurposes
(8) Destroyed