not to leave a field of main form blannk (1 Viewer)

[hfsitumo2001]

Hello, How can we make it not to leave a field blank such as customerID from the Main form, before it goes to the sub form as I marked in red in the attached form. Because when we got in the sub form, I can not get back to the previous field ( CustomerID}. and get stuck, so I delete the transaction.

On other thing, could be a silly question, but since English is not my native language, I need to ask this. What is the best name of the form for supplies taken out and supplies received in the ware house.
Is it Supplies Check out Form for going out, and Supplies check in form for supplies coming in

Thank you for any helps.

Frank\

 

[Gasman]

Why can you not go back to that combo?

 

[The_Doc_Man]

Frank, your best bet is to tag the field's properties as REQUIRED. Access won't let you save the record if it is empty, and if that other thing is a sub-form, you can't leave the main form - because going to a sub-form saves the main form.

 

[jdraw]

Frank,

You can also use the BeforeUpdate event of the form to validate the values of each control to ensure the values meet your requirements. eg, must not be empty, must be less than 10 etc.

 

[MajP]

I use this code which hilights all the required missing fields.

Solved - Form Validation using .Tag with highlighted controls on close

Hello, I am trying to validate controls on y form that if their .tag is "Required" and they are null or =0 then highlight the border red and if there is data stay the same. I found a piece of code years ago and am trying to implement it but cant figure out what to change for the below revision...

www.access-programmers.co.uk

Here is a demo using the linked code. Go to a new record and enter part of a record.

 

[hfsitumo2001]

Hi MajP, I want it not to let us move to the next field in the subform, because when when we moved, there is an error message from the access itself to say we should fill the customer ID, but even I stuck I can not go back, unless when my cursor in the description field i press escape than i delete the transaction in the main form and start again for the new transaction ID and fill every thing as usual.

So actually I need to not letting me go description field unless I filled the customer ID. What is the code in the property of customer ID

Thank you for any help.

Frank

 

[Pat Hartman]

The problem is the code in the close button. For now, remove all the code except

DoCmd.Close acForm, Me.Name

When you close the form, Access will automatically run the form's BeforeUpdate event. After you get this working, we can help you to add code to get around an error caused by closing a form when there is a validation error.

PS you have an embedded macro and not all of your required fields are tagged with "required".

 

[hfsitumo2001]

I do not have this code on the before update event Pat, what do you mean. Is it pertaining to my case?.

Thank you

 

[The_Doc_Man]

If you really want to stop the person from even leaving the control, you put a test in the control's .LostFocus event and if it is deficient, you do a .SetFocus to itself.

 

[MajP]

because when when we moved, there is an error message from the access itself to say we should fill the customer ID

If you put the code in the before update event you cannot go to the next record and you can avoid the error.

 

[Pat Hartman]

Most people dislike being constrained. Therefore, I do not constrain them by stopping them from leaving controls except under rare circumstances as I said earlier and I do it by using the BeforeUpdate event. You especially don't want to annoy people who didn't actually modify a control so I would not use the LostFocus event since that runs regardless of whether the control was modified or not.

I do not have this code on the before update event Pat, what do you mean. Is it pertaining to my case?.

Look again. You are calling a procedure that loops through controls and if their tag property is "required", you ensure they are not null. You also call this code from your close button which is redundant. The code will run there and it will run again when the record is saved.

Did you try my suggestion to remove ALL code except the close command? We can go from there once that works.

 

[hfsitumo2001]

If you put the code in the before update event you cannot go to the next record and you can avoid the error.

If you really want to stop the person from even leaving the control, you put a test in the control's .LostFocus event and if it is deficient, you do a .SetFocus to itself.

what is the code of a SetFocust Sir

Thank you.

Frank

 

[Pat Hartman]

If you properly use the BeforeUpdate event you do not need code IN ANY OTHER EVENT!!!!!!!!!!!! You CANNOT save a record that fails your validation edit if you do it correctly.

Private Sub cmdClose_Click()
  Dim rtn As Long
  If DataValid(Me) Then
    DoCmd.Close acForm, Me.Name
  Else
    rtn = MsgBox(" Select OK to Close without saving, or CANCEL to complete form.", vbOKCancel, "Validate Data")
      If rtn = vbOK Then
        Me.Undo                             ''''''''''''''''''  YOU MUST clear the changes before exiting the form.
        DoCmd.Close acForm, Me.Name
      End If
  End If
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Not DataValid(Me) Then
    Cancel = True
  End If
End Sub

I fixed the immediate error but your method is still INCORRECT. If you had stepped through the code, you would have seen that without the undo, the close command causes Access to attempt to save the data because the form is STILL dirty and you CANNOT get past the validation in the BeforeUpdate event.

I will try to remember to post a more complete solution later. If you don't see one later today, please pm me. I'm occupied for a few hours.

 

[hfsitumo2001]

If you really want to stop the person from even leaving the control, you put a test in the control's .LostFocus event and if it is deficient, you do a .SetFocus to itself.

The doc man, can you give me the code on LostFocus event? to let not people leave it blank?

 

[Pat Hartman]

I guess you don't believe me The BeforeUpdate method prevents the data from being saved no matter what AND does what you want since it can prevent focus from leaving the control if you use the control's BeforeUpdate event. Code in the lost focus event will NOT stop the bad data from being saved. You'll get your error message but the bad data will be saved.

Not all kinds of validation can be effectively performed in control events which is why I almost always put all validation into a single event - the Form's BeforeUpdate event for convenience. For example, you can't validate for "presence" in the control event? Why? Because if focus never entered the control NO control events will run. It is also awkward to validate relationships using a single control event since you can't add data to two events at one time so one of the events will be null and that means you need to work around that issue. So if ReceivedDT must be >= OrderDT, the best place to do the validation is the Form's BeforeUpdate event. Otherwise you need to do the validation in BOTH controls AND you need to allow for null in either field. If you validate in the Form's BeforeUpdate event, you can make assumptions that are valid.

MS created specific events for specific purposes. The events are not random. Use the events correctly or not. Your choice as long as you don't care if you save bad data.

 

[hfsitumo2001]

I guess you don't believe me

Pat, I believe you, the reason is because if it is still in the middle of the road, as you can see in the sub-form, if user key in the quantity, it will automatically update the Quantity on hand even though the record was not saved. And my quantity on hand is not formula, but replaced with the new quantity on hand minus or plus transactions amount. So that is why I prefer, the cursor will go to the control first after filling out the date, then when it is already in the said control, it wont' let you leave it blank or you can not move to the next field/ item description in the subform.

Thank you for your help

Frank

 

[Pat Hartman]

If the quantity on hand is being updated at the wrong time, you need to fix that first! Learning how to use form events is critical to doing this correctly and it is bad enough to store the quantity on hand but even worse to update it incorrectly. You are never going to be sure the amount is correct unless you get control of the updates. Stopping the cursor from leaving the control is NOT going to solve the problem.

I told you how to do this using the form's BeforeUpdate event. Did you try that solution. It doesn't matter whether the focus can leave the control. What matters is that the form's BeforeUpdate event, will STOP the record from being saved if the quantity is missing. You can't do this kind of validation by using the Control's BeforeUpdate event because unless you have locked all controls on the form and you are forcing the cursor to move field by field, there is no way to ensure that focus ever ended up in the quantity control and if focus never entered the quantity control, then the lost focus event for that control is NOT GOING TO RUN so your code won't even run!!

Personally, I don't care what you do. I'm not the one whose job will be on the line. But, I'm telling you that your current solution is wrong and preventing focus from leaving the control doesn't solve it because closing the form or the database WILL save the record because your validation is not being done in an event that will prevent the bad data from being saved.

Beyond that, updating the quantity on hand and storing the calculated value is fraught with danger. The correct/safe way to manage inventory is to create transaction records. Then you sum the transaction records to get the quantity on hand when you need it. This gives you an audit trail and minimizes the potential for multiple users updating the balance at the same time leaving the balance incorrect.

 

[hfsitumo2001]

If the quantity on hand is being updated at the wrong time, you need to fix that first! Learning how to use form events is critical to doing this correctly and it is bad enough to store the quantity on hand but even worse to update it incorrectly. You are never going to be sure the amount is correct unless you get control of the updates. Stopping the cursor from leaving the control is NOT going to solve the problem.

I told you how to do this using the form's BeforeUpdate event. Did you try that solution. It doesn't matter whether the focus can leave the control. What matters is that the form's BeforeUpdate event, will STOP the record from being saved if the quantity is missing. You can't do this kind of validation by using the Control's BeforeUpdate event because unless you have locked all controls on the form and you are forcing the cursor to move field by field, there is no way to ensure that focus ever ended up in the quantity control and if focus never entered the quantity control, then the lost focus event for that control is NOT GOING TO RUN so your code won't even run!!

Personally, I don't care what you do. I'm not the one whose job will be on the line. But, I'm telling you that your current solution is wrong and preventing focus from leaving the control doesn't solve it because closing the form or the database WILL save the record because your validation is not being done in an event that will prevent the bad data from being saved.

Beyond that, updating the quantity on hand and storing the calculated value is fraught with danger. The correct/safe way to manage inventory is to create transaction records. Then you sum the transaction records to get the quantity on hand when you need it. This gives you an audit trail and minimizes the potential for multiple users updating the balance at the same time leaving the balance incorrect.

Beyond that, updating the quantity on hand and storing the calculated value is fraught with danger. The correct/safe way to manage inventory is to create transaction records. Then you sum the transaction records to get the quantity on hand when you need it. This gives you an audit trail and minimizes the potential for multiple users updating the balance at the same time leaving the balance incorrect.

You are right Sir, could you give me the simple database that will not keep the Quantity balance, but to make a calculated field for the balance on hand. I think the same way as bank account balance.

Thank you

Frank

 

[jdraw]

Frank,

You posted, "Since English is not my native language....". Let me suggest that you create a post/question/comment in your native language, then using Google translate, get the English translation of your text and post that. Google translate may not be 100%, but many of us have found it more than adequate for communication. Similarly, if/when you get a response that you may have difficulty understanding the English version, google translate to you native language.

Curious--- what is your native language?

As for Quantity on Hand, this is a very popular reference by Allen Browne

 

[hfsitumo2001]

Curious--- what is your native language?

My native language is Indonesian Jdraw, but we immigrated to CA 7 years ago, and since we came here was not young, so is not easy to be fluent in English.