Validate Form Before Navigating To Next Record

Dec 7, 2006

Hi

I was wondering if anybody could help with this. I have searched through this forum and there doesnt seem to be an answer to this.

I have a form that i would like to validate. At present i have put the data validation on the save button which triggers a script that checks to see if certain boxes have been filled in on the form. I can only get this to work be attaching it to the onclick event on a save button, however, what i really want is to be able to ditch the save button (since access writes to the DB as it goes along) and have this script triggered whenever the user tries to navigate to either a new record/ another existing record or close the form

I tried putting this on the beforer update event, but this does not work, additionally tried doing before update event with the code inside an if me.form.dirty = true statement
this did not work either, has anybody got any suggestions

Please see my code below

Thanks

Marcus

Dim sDeliveryto As String
Dim sDeliveryValid As String
Dim sDept As String
Dim sDeptValid As String
Dim sReq As String
Dim sReqValid As String
Dim sReqNo As String
Dim sReqNoValid As String
Dim sReqPoint As String
Dim sReqPointValid As String
Dim sOrderDetailsValid As String
Dim sQuantity1 As String
Dim sQuantity1Valid As String
Dim sDetails1 As String
Dim sDetails1Valid As String
Dim sPrice1 As String
Dim sPrice1Valid As String
Dim sSupplier1 As String
Dim sSupplier1Valid As String
Dim sCostCentre1 As String
Dim sCostCentre1Valid As String
Dim sAccountCode1 As String
Dim sAccountCode1Valid As String
Dim sAuth As String
Dim sAuthValid As String



sDeliveryto = Me.TBDeliveredTo & ""
sDept = Me.TBDept & ""
sReq = Me.tbrequisitioner & ""
sReqNo = Me.TBRequisitionNo & ""
sReqPoint = Me.TBReqPoint & ""
sQuantity1 = Me.TBQ1 & ""
sDetails1 = Me.TBD1 & ""
sPrice1 = Me.tbup1 & ""
sSupplier1 = Me.tbs1 & ""
sCostCentre1 = Me.ccc1 & ""
sAccountCode1 = Me.tbac1 & ""
sAuth = Me.TBAUTH & ""



Select Case sDeliveryto
Case Is = ""
Me.TBDeliveredTo.BackColor = "8421631"

sDeliveryValid = "Invalid"

Cancel = True
Case Else
sDeliveryValid = "valid"
Me.TBDeliveredTo.BackColor = "16777215"

End Select


Select Case sDept
Case Is = ""
Me.TBDept.BackColor = "8421631"

sDeptValid = "Invalid"

Cancel = True
Case Else
sDeptValid = "valid"
Me.TBDept.BackColor = "16777215"

End Select

Select Case sReq
Case Is = ""
Me.tbrequisitioner.BackColor = "8421631"

sReqValid = "Invalid"

Cancel = True
Case Else
sReqValid = "valid"
Me.tbrequisitioner.BackColor = "16777215"

End Select

Select Case sReqNo
Case Is = ""
Me.TBRequisitionNo.BackColor = "8421631"

sReqNoValid = "Invalid"

Cancel = True
Case Else
sReqNoValid = "valid"
Me.TBRequisitionNo.BackColor = "16777215"

End Select

Select Case sReqPoint
Case Is = ""
Me.TBReqPoint.BackColor = "8421631"

sReqPointValid = "Invalid"

Cancel = True
Case Else
sReqPointValid = "valid"
Me.TBReqPoint.BackColor = "16777215"

End Select

If Len(sReqPoint) < 6 Then
sReqPointValid = "Invalid"
Me.TBReqPoint.BackColor = "8421631"
Cancel = True
Me.lblReqPoint.Visible = True

Else
sReqPointValid = "valid"
Me.TBReqPoint.BackColor = "16777215"
Me.lblReqPoint.Visible = False
End If

Select Case sQuantity1
Case Is = ""
Me.TBQ1.BackColor = "8421631"

sQuantity1Valid = "Invalid"

Cancel = True
Case Else
sQuantity1Valid = "valid"
Me.TBQ1.BackColor = "16777215"

End Select


Select Case sDetails1
Case Is = ""
Me.TBD1.BackColor = "8421631"

sDetails1Valid = "Invalid"

Cancel = True
Case Else
sDetails1Valid = "valid"
Me.TBD1.BackColor = "16777215"

End Select


Select Case sPrice1
Case Is = ""
Me.tbup1.BackColor = "8421631"

sPrice1Valid = "Invalid"

Cancel = True
Case Else
sPrice1Valid = "valid"
Me.tbup1.BackColor = "16777215"

End Select

Select Case sSupplier1
Case Is = ""
Me.tbs1.BackColor = "8421631"

sSupplier1Valid = "Invalid"

Cancel = True
Case Else
sSupplier1Valid = "valid"
Me.tbs1.BackColor = "16777215"

End Select

Select Case sCostCentre1
Case Is = ""
Me.ccc1.BackColor = "8421631"

sCostCentre1Valid = "Invalid"

Cancel = True
Case Else
sCostCentre1Valid = "valid"
Me.ccc1.BackColor = "16777215"

End Select


Select Case sAccountCode1
Case Is = ""
Me.tbac1.BackColor = "8421631"

sAccountCode1Valid = "Invalid"

Cancel = True
Case Else
sAccountCode1Valid = "valid"
Me.tbac1.BackColor = "16777215"

End Select

Select Case sAuth
Case Is = ""
Me.TBAUTH.BackColor = "8421631"

sAuthValid = "Invalid"

Cancel = True
Case Else
sAuthValid = "valid"
Me.TBAUTH.BackColor = "16777215"

End Select


' Display message box warning

If sDetails1Valid = "Invalid" Or sQuantity1Valid = "invalid" Or sReqPointValid = "Invalid" Or sReqNoValid = "Invalid" Or sReqValid = "Invalid" Or sDeptValid = "Invalid" Or sDeliveryValid = "Invalid" Or sPrice1Valid = "Invalid" Or sCostCentre1Valid = "Invalid" Or sAccountCode1Valid = "Invalid" Or sAuthValid = "Invalid" Then
MsgBox "Please fill all highlighted fields on the form!!!!!"
Else

DoCmd.Save
MsgBox "Is all the information Correct?", vbOKCancel


'open report

Me.btnClose.Visible = True
Me.btnInvoice.Visible = True
Me.btnDeleteClose.Visible = False




End If

View Replies


ADVERTISEMENT

Record Navigating In The Form View Question

Oct 4, 2005

Hi, Sorry noob here with a noob question... I built my first database for my work, and I want buttons that can navigate through the records on the form view, but I want them to be like A,B,C,D etc... When I click the button I want it to go to the first last name in the record of that letter of alphabit.... I know it can be done because at my previous employment they had that feature. Please help... I can't figure this out... :confused: Also, how do I import IMT forms into an access database???

View 1 Replies View Related

Forms :: Navigating To A Record In Main Form

Aug 10, 2013

I have a form and a subform for data entry.It is often the case that not all the data may have been entered and so a user might need to go to recordID 24 in 30 already entered main records.However, he needs to get to that record via a (unique) 1-1 field. How do I create the form so that as well as being able to go to the first, the last, the next, the previous record, I can go to a record of my choice.

View 5 Replies View Related

Modules & VBA :: Retrieve / Validate Data On 2nd Form Based On Record Selected On 1st Form

Jul 29, 2013

Can I look up and verify data on a "second" form based on a selected record from first (still open) form.

I am trying to allow users to select a User Name from a combo box list and then open "Change Password" form when they select "Change Password" for that selected user name.

My problem is that I can't figure out how to associate and verify the data tied to the user name selected on the previous (Login) form ( I am trying to validate the old password tied to that selected record).

I have the first login form created, and it's working just fine. I also have the change password form created (and it's displaying the user name selected from the first form using:

Code:
Private Sub Form_Load()
With Forms![frmLogin]![cboUserName]
Me.txtPwdChgUserID = .Column(2, .ListIndex)
End With
EndSub

I also have the code written to validate and confirm old password, new password and validate new password (when the save button is clicked). I have yet to update the password with the new password (still trying to figure that out).

Attached zip file has screen shots of the two forms.

View 3 Replies View Related

General :: Record Is Saved Without Navigating

Sep 19, 2013

When using Access 2007, I have a new record button, but that record is only written to the db once you navigate away from it. How do I write immediately to the DB, so that record is saved without navigating away from it. I believe I can use ADO or DAO but I'm having difficulties.

Private Sub Command71_Click()
DoCmd.GoToRecord , , acNewRec
MaxValue = DMax("[Record Num]", "Joblog")
Text64.Value = MaxValue + 1

View 9 Replies View Related

Possible To Validate A Single Record In A Field

Aug 19, 2014

I've done some looking into how to validate in Access and I found how to do it for an entire field but I was wondering if it's possible to validate a single record in a field.

View 6 Replies View Related

General :: Check Or Validate If There Is Record In SQL Statement?

Mar 13, 2014

Payroll main form to post attendance record. Before I open the attendance form, I wanted to check if there is a record in the sql statement.

The code below produces error MS Acces object "Variable or With Block Variable Not Set"

Code:
Private Sub cmdPostAtt_Click()
Dim stSQL As String
Dim dbHBK As DAO.Database

[Code].....

View 3 Replies View Related

Navigating Records In A Form Using A Filter

Feb 22, 2005

Hello,

I need to navigate through all records for a table where a <field> = a certain "string". Ideally I would have a next record button that would know to ignore records that do not meet my criteria?

Simply put,
Next available record where <field> = "string"?

Is there anyway to do this?

Regards,
Chris

View 2 Replies View Related

Trouble Navigating Form/subform

Aug 31, 2005

I'm having a problem with a form/subform. The table I'm using includes the fields: date, personnelID, status, comments. The main form has a single field, date. The subform has the fields date, personnelID, status, comments. I have the form/subform linked using the Date field, because there are multiple instances of the same date in the table, I have to keep clicking the navigation buttons on the main form to go through all the duplicate dates to get to the next one. Is there a way I can filter the Date field on the Main form to only show one copy of each date? I had thought about creating a new table with only a date field and no duplicates, then just append all dates from the main table to this one and use it to go through the dates, however I'd rather not have to make an extra table just for this. This is basically for personnel accountability, I want to be able to select a date on the main form and display the "daily status"(present, on leave) for each person in the subform.

Even better would be if I could create a next and previous button to switch the date on the main form(I know how to do this.) but how would I get the subform to update the records to the date on the main form and keep it from going to dates that don't exist?

View 1 Replies View Related

Navigating Records On Unbound Form With VBA

Jul 1, 2013

I have a form that reads records from a query.. It loads the first record into the form, without issue.

Code:
Sub FormLoaded()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("Results") ' Query we want
Forms("frmmainnew").lbladdUser1bad.Visible = False

[Code] ....

Now I have have 2 buttons at the bottom of the form, one for next record and another for previous record...

Code for next record

Sub NextRecordbtn()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("Results") ' Query we want
r.MoveNext
If r.EOF = True Then

[Code] ....

I know that I have 3 records that result in the query named "Results", the next record button will bring the form from record 1, to record 2... however it will not move from the 2nd to the 3rd record...

View 14 Replies View Related

Loosing Lines On Main Form After Navigating To Another Tab

Sep 11, 2006

I have a main form that displays four lines of data. I have a tab control with 3 tabs. The form displays perfectly until I navigate to one of the other tabs and back to the main tab. It appears to scroll down a couple of lines and I loose the top line of data.

Any idea what is happening or how to stop it?

:confused:

View 1 Replies View Related

Forms :: Navigating Fields In Tabbed Form

Jan 22, 2014

I have a form which consists of four (4) tabbed sheets. The first sheet is the main form and the other three sheets are the linked subforms.

Pressing the tab key on the keyboard navigates from field to field in each individual form; however, I would like that when I get to the last field of each form, pressing tab moves to the next sheet or the first field of the next subform.

The problem I am getting is that when I get to the last field of the main form and/or subforms, hitting the tab button creates a new record in the specific form, which I do not want. I want to just tab to the next subform.

View 14 Replies View Related

Validate Form Field Entry

Feb 18, 2005

i would like some help on how to validate a user's entry in a form. i have a query that calculates number of hours remaining for a particular customer. when a user submits the number of hours used on the form, i want to check that this value does not exceed the number of hours they have remaining. and display a message box if they dont have enough hours remaining. any suggestions on how best to do this?
the query has a where statement like 'WHERE customer = customer_code' so customer_code is entered by the user. how do i set this so that the value for customer_code is taken from a field on the form?

View 2 Replies View Related

Forms :: How To Validate A Field In Form

Jul 8, 2014

I need to validate field in form so user cant not skip that data

How to do that?

View 14 Replies View Related

"#Deleted" When Navigating To New Record

Jun 26, 2006

Hi,

I have a data entry form/subform with an embedded graph allowing users to preview the data they input. When I navigate to a new record in this form the the fields in the form and subform echo "#Deleted". Yet the data is still there, it is just not shown in this form.

I have tried copying all the controls and code into a new form but the same error still occurs!

Any thoughts?

Bobadopolis

View 3 Replies View Related

Locked Main Form - Validate 1 Subform With Another?

Oct 24, 2005

I have a main form that has 3 fields. They are all locked fields, only to show the user the "person" record that they are looking at. They update to subforms on a tab. The first subform is activities (like from a mailing - received return no forwarding address, received updated address, called us, etc)

The other subform is for addresses, original and updated.

There is a date field for the date of any updated addresses.

What I need to do is allow users to enter activities and move around from main record to main record. The important thing is that if they enter an activity on the subform that is for receiving an address change, I need to make sure that they do not leave the main record, without entering in an updated address for that same date, on the address subform.

The activity subform has a date field too, so the validation part seems like it wouldnt be too hard...

IIF ?subform-activities-field-activitytype = "updated address" (3/code to lookup table) AND ?subform-address-field-date = grrr this is where I get confused.

Then msg "Stop, you must add the updated address for the activity of receiving an address change"
cancel = true

end if

make sense?

Any help would be gratefully appreciated!

View 4 Replies View Related

Modules & VBA :: Search And Validate For Login Form

Nov 27, 2014

I have a table called 'Klanten' which contains the rows 'password' and 'login' (and several rows not needed for this form)

So I'm trying to make a login form which first checks if something is entered (this part of the code seems to work).

Private Sub Knop13_Click()
'Check to see if data is entered into Username
If IsNull(Me.Username) Or Me.Username = "" Then
MsgBox "gelieve een login in te voeren.", vbOKOnly, "Required Data"
Me.Username.SetFocus

[Code] ....

But from then on i seem to have some issues.. The part of the code underneath seems to only work for the first 'login' and 'paswoord' in my table called "Klanten".

-Username is the name for the field where they enter their 'login'.
-Password is the name for the field where they enter their 'paswoord'

If Username.Value <> DLookup("[login]", "Klanten", "[Username]='" & Username & "'") Then
MsgBox "Invalid Username. Please try again.", vbOKOnly, "Invalid Entry!"
Exit Sub
End If

If Password.Value <> DLookup("[wachtwoord]", "Klanten", "[Password]='" & Password & "'") Then
MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Entry!"
Exit Sub
End If

Then as last part i would like to goto another form called 'Mainmenu' if both the Login and the Paswoord is correctly entered in the fields Username and Password. Here i have the most issues as this doesn't seem to do anything at the moment

If Password.Value = DLookup("[wachtwoord]", "Klanten", "[Username]='" & Username & "'") And Username.Value <> DLookup("[login]", "Klanten", "[Password]='" & Password & "'") Then DoCmd.OpenForm "Mainmenu"
End Sub

View 1 Replies View Related

Navigating Combo Box

May 8, 2006

If an answer to this is out there, sorry. I haven't had luck finding anything.
I have a form with an unbound combo box. When you select an item from the box, the objects on the form and subform fill in based on the selection. The person using this would like to click arrows to navigate through the combo box. How do I do this?:confused:

View 2 Replies View Related

Navigating A Filtered List

Jun 8, 2005

I have a form that contains a pretty large list of items and several columns and the list can be sorted by each of these columns. When a user double-clicks on a particular item, another form pops up that lets the user view/modify the item. The problem I'm having is that I still want to be able to use the navigation buttons in that second form, but right now its just saying 1 of 1 (Filtered).

The way I'm applying the sorting is by updating the Rowsource field of the list with an "Order By" statement

Any help would be appreciated

Adam

View 2 Replies View Related

Navigating Forms Using Menus

Sep 9, 2005

Hi
with the help of this site, i have made big advances in learning and adapting code, in building my access FE on an SQL BE.

however, i have a problem, i cant find any information on in how to resolve.

i am using menus to navigate between forms, but i cannot synchronise the critical value between the forms.

so i am on a form with critical value, say hullID

i use a menu command to open another form, but i need the form to open with the same hullID as the critical value as the previous form which is about to close.

because i can go to the new form from any form, using the menu command, i cannot directly link the critical value,

is there a way to use the critical value from the current focus form, to the new opening form

many thanks in advance

View 8 Replies View Related

Forms :: Buttons For Navigating Datasheet

Apr 10, 2015

I have made an access database for doing inventory at work. We have an inventory of approx. 700 items that need to be counted each month. I have a subform that is in datasheet view, it has Inventory IDs, product descriptions, UOMs, and a field to enter quantities. On the parent form are pictures and extra descriptions linked to each item in the subform.

The Inventory IDs all start with a 3 letter alpha code for which class of item they are PLU for plumbing, FAS for fasteners, ELD for electrical device, etc. There are about 15 different classes of these inventory items. The rest of the InvIDs are 4 digits: (PLU5453 or ELD1123)

We are using tablets to do the inventory counts, so to search for an item we can swipe through the datasheet to find the InvIDs we are looking for and then enter the quantity. After our initial inventory my supervisor asked if I could add buttons to jump to the first item in a particular class of item such as PLU0001. He showed me his address book in his phone as an example, how he can push a P button to bring him to the first name that starts with P.

I considered tabs for each class, but he wants to navigate to other items by just swiping also. I tried to make a Macro that would use Go To Record but was unable to make that work.

View 1 Replies View Related

Forms :: When Navigating Tabs - Preventing The Loss Of First Tab

Aug 7, 2014

First thing: I have a navigation form that leads through 5 subforms on my database. One is a data entry form, the rest are various informative forms with explanations, images and other text mainly. In this scenario, a user enters data on the first tab but doesn't finish it. The user clicks over to check up on some guidelines on the other tabs and then comes back to the first tab for find that all of the fields he entered reset and he lost everything.

Is there a way of preventing this from happening? Plan A would be for me to allow the user to switch around tabs without losing the data he or she entered into textboxes or dropdowns. Plan B would be for me to have a popup that appears when he tries to switch tabs that warns him of data loss and that he should save before moving tabs.

PS: One other thing: I have the standard Access wizard search button on my first tab that allows the user to search records. This works well, but I'd like for the search box to close immediately after a search turns up a matching record. This seems odd, but the users requested it because manually closing the search box after every search can add up to a lot of wasted time.

View 8 Replies View Related

Command Buttons Previous And Next Not Navigating To Records

Dec 23, 2014

I have a custom command button cmdPreviousRec and cmdNextRec for navigation, but when i click the cmdPreviousRec its not navigating to the records and the cmdNextRec is up to 2nd record only.

Code below is the i've working:

Private Sub cmdNextRec_Click()
On Error Resume Next
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblAMHMace")
If Not rs.EOF Then
rs.MoveNext

[Code] ....

View 5 Replies View Related

Records Forgetting Data From Cascading Combo After Navigating Away

Oct 11, 2005

Hi all, complete Access n00b here requiring some assistance if possible with the dreaded cascading combos. Would appreciate any help I can get. I've tried searching but can't find this particular problem mentioned (am probably searching for the wrong thing, but anyway...) Here is my situation:

I have a form [Component] with a simple cascading combo box scenario [cboCategory] and [cboSubcategory].

[cboCategory] has as its row source -
SELECT tblCategory.categoryid, tblCategory.Category FROM tblCategory ORDER BY tblCategory.Category;

Control source is unbound.
I have Me.cboSubcategory.Requery as an afterupdate event.

[cboSubcategory]'s row source is -
SELECT tblSubcategory.subcategoryid, tblSubcategory.subcategory FROM tblSubcategory WHERE tblSubcategory.Categoryid=Forms!Component!cboCateg ory ORDER BY tblSubcategory.Subcategory;

Control source is bound to SubcategoryID in tblComponent.

I have set up [tblCategory] as the one side of a one to many relationship with [tblSubcategory] and [tblSubcategory] as the one side of a one to many relationship with [tblComponent]. [SubcategoryID] (foreign key) is the only piece of 'category' information I am storing in [tblComponent]. I presume that's the correct thing to do since you can calculate the category from the stored subcategoryID.

The problem is that the form only seems to remember the most recent combo choice so if i fill in one record, navigate to another use the combos again, then navigate back, the original combo choice has been overwritten in the form record (although the underlying tblComponent seems to have the correct data written to it). In other words ALL records on my form display the last values that were entered using the combo boxes and not the correct values. Is this normal? I hope not, and if not then what am I doing wrong?

Thanks in advance :)

View 10 Replies View Related

Forms :: Prevent User Navigating From A Field If Entry Is Invalid

Mar 5, 2013

I need to ensure that the data entered into a field on a form is correct. The field contains an email address and I have a function to check if the format is correct.

If the format is incorrect I need the cursor to return to the field in that record. The form is continuous.

What event should I use?

View 2 Replies View Related

Validate Check Box

May 5, 2006

I have check box [yes/no] on a form. I want to be able to tick check box only if today is Friday or Saturday. Any ideas please?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved