Modules & VBA :: How To Validate Three Condition Before Providing Error Msg
Feb 11, 2014
I am trying to write following code, I want Msg to pop, when all three condition are true, but it not working
'''Non Budgeted Projects need Explanation and Variance class"
If Me.ID.Value >= 90000 And and Me.Variance_Class = "" and Me.Comments_Explanation_Delta_____100K = "" Then
MsgBox "This project is Unbudgeted. Please Add 'Variance Class' and provide Explanation why this project is Unbudgeted project has been added.", vbExclamation, "Rules Checker..."
CheckRules = False
GoTo Exit_CheckRules
View Replies
ADVERTISEMENT
Jan 15, 2015
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation
Variables:
@SelectedDate (date format) (taken from a form field)
@SelectedVessel (nvarchar(max) format) (taken from a form field)
@SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
View 4 Replies
View Related
Oct 14, 2014
I am pushing some data to Excel from an Access query. When the data is in Excel I reformat the sheet by changing the fonts, applying borders and cell formats - I have got all of this to work fine.
The one thing I am struggling with is applying conditional formats. I am pretty sure it is something to do with incorrectly referencing the applcation/sheet. An extract of what i think to be the key parts of the code are below.
....
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
...
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True
[Code] ....
View 8 Replies
View Related
Apr 13, 2014
I am trying to validate that the user selects at least one checkbox out of three on the form in Access 2010. They can select one, two or even all three if they wish, but at least one must be checked. I am using the following code attached to the click event of a command button. It was fine but seems to have stopped working. The control names are generic to illustrate my code. The form is unbound:
if (chk1 + chk2 + chk3) = 0 then
Msgbox "Please select at least one checkbox"
exit sub
end if
View 14 Replies
View Related
Aug 5, 2014
I have a textbox where I need an afterupdate event, which should check that input is 3 letters followed by 3 numbers. The total is 6 characters, but always 3 letters + 3 numbers. If condition not met, a msgbox will be shown, for the user to input correctly, before being able to continue.
View 1 Replies
View Related
Jan 20, 2014
I would like to ask how can i validate the username and password in a textbox? I have a frmLogin and frmMain.
I have also a table called 'tblUsers' with column fields 'username and password'.
If username is not found in database then a msgbox will prompt 'Username is not yet created'.
If username is okay and password is incorrect then msgbox will prompt 'Incorrect password'
If username and password is okay, then a msgbox will prompt 'Successfully login' and will continue to frmMain.
View 3 Replies
View Related
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
Jan 20, 2014
I'm new to access vba and I'm trying to create a login form. I have already my code to login but i want to validate the password in 'case sensitive' basis. Below is only what I've got so far.
Code:
Private Sub cmdLogin_Click() Dim login_validation As Variant login_validation = DLookup("Password", "tblLogin", "Username='" & Nz(txtUsername.Value, "") & "'") If Nz(login_validation, "") <> Nz([txtPassword].Value, "") Then MsgBox "Incorrect Password. Please try again." txtUsername.Value = "" txtPassword.Value = "" txtUsername.SetFocus Else MsgBox "Hi " & txtUsername.Value & "," & vbNewLine & vbNewLine & "you have successfully login!" DoCmd.OpenForm "frmMain" End If End Sub
View 5 Replies
View Related
Dec 24, 2013
I am trying to pass a date field from a Form and check if this date already exists within a table if so run this macro.
This is what I have so far...
Private Sub Command38_Click()
If DCount("*", "TblDietPlan", "[MealDate] = <> txtCusDate Then
stDocName = "McrPrint_LabelsWklyCR"
DoCmd.RunMacro stDocName
[Code] ....
View 2 Replies
View Related
Apr 15, 2014
I am having an issue using DCount to validate against 3 fields within my database. I have a booking form which contains a Staff member, viewing slot, and Viewing Date which is used to book property viewings.
I want the form to check that the booking doesn't already exist when the process booking button is pressed.
I am using the following statement:
Code:
If DCount("*", "Viewing", "[Staff_ID]=" & Me.[Staff_ID] & " AND [Viewing_Period] = " & Me.Viewing_Period & "' AND Viewing_Date = '" & Me.Viewing_Date) & "'" > 0 Then
MsgBox "Cannot book, booking already exists", vbCritical
End If
I always get the error "Syntax Error (Missing Operator)".
View 3 Replies
View Related
Mar 8, 2013
I've been working on trying to get this code to work as expected for days. I'm trying to find duplicates (I can't use primary keys or indexes alone to weed out duplicates due to the structure of the tables involved) in a subform as a user enters data. As soon as a project number is added, the code is supposed to count the number of records that contain that particular project number as well as a category number (there can be multiples of the same project numbers as long as their category numbers are different). This is the code I am using in the "Before Update" event of the field in the subform:
Private Sub ProjectID_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String
Dim RecCount As Integer
strCriteria = "([ProjectID] = " & Me.ProjectID & ") AND ([CatID] = " & Me.CatID & ")"
RecCount = DCount("[ProjHrsID]", "tblProjHrs", strCriteria)
[Code] .....
What could I be doing wrong? Nothing about this code seems to work properly - even the Undo and Cancel=True is a problem (I get the "No current record" error).
View 2 Replies
View Related
May 21, 2014
I got this code to run correctly, which pulls records where they are not junk(can be seen at end of where condition), the only issue is that the "AND" does not turn blue. Is this a problem?
Code:
If Not IsNull(Me.txt_last_rec_id) Then
'DoCmd.SetWarnings = False
SQLText = "INSERT INTO tbl_batt_id " & _
"(record_num, bat_id, manufacture, date_code, barcode, status) " & _
"SELECT " & Me.record_num & ", bat_id , manufacture, Date_Code, barcode, Status " & _
"FROM tbl_batt_id " & _
"WHERE record_num = " & Me.txt_last_rec_id & " And [Status] <> 'Junk'"
[code]....
View 3 Replies
View Related
Dec 15, 2013
Everything I have goggled and tried doesnt work How do I convert the where condition to VBA
Code:
Form name frmMap
View form
Filter name
Where condition [chid]=[forms]![frmCurchesAll]![chid]
View 12 Replies
View Related
Apr 3, 2014
I have one case about validation in the form
example: I have entry in table this mention below:
No.Register | Code_machine
________________________
001 | X01
002 | X01
001 | X02
003 | X02
003 | X01
002 | X03
How to make validation for this case when I will submit the data 001 and X01, the system will be cancel and show warning message : "Data Duplicate!".and of course with this happen, the user will be know what they will submit, it have been submit into table before by other user. if I will do make with one validation like noregister as checking data and use the DLookup, this is no problem.
View 6 Replies
View Related
Oct 24, 2013
Is it possible to set any label id as query parameter in sql code or access?
View 5 Replies
View Related
Apr 8, 2015
I Have one main form in access 2003 i want to use Dlookup function to 1 text box with 2 criteria is it possible?
My Formula:
Text67. Value = DLookup("Balance", "dbo_WbookEdit", "[Lotno] = '" & Forms![Edit]![LotNo] & "'" And " & [Description] = '" & Forms![Edit]![Description] & "'")
View 6 Replies
View Related
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
Aug 13, 2014
I have inventory database, in database there is "NextScheduledMaintenance" field.
I want access to send email automatically when scheduled maintenance date due.
View 7 Replies
View Related
Jun 25, 2014
Heres what I need to do.
--> open a recordset
--> read through the records
--> check to see if two fields matches e.g (qty=qty_completed)
--> if all records in recordset meets the above condition then run update statement
I want to put this code inside Form_Close. The code that I'm using checks the two fields for the condition and runs the update statement if condition is met. Problem is this a continuous form and I want all records need to meet the condition before it execute update statement.
Using Access 2010 btw.
View 8 Replies
View Related
Feb 11, 2015
I am trying to append some data from an excel sheet. Following code used for same and successful. However, i need to put few condition while importing the data to the Access table. i.e;
Table : tblMain
Filed : Vessel, Voyage, contianerNo, ......, ......, .....,
while importing, need to check whrt "containerNo" which is importing already in the table under the particular voyage. If yes error msg should pop up warning that the particular container is already exists. If same container number is already exists in the same table under a different voyage, data should be imported without any error.
Code
Private Sub Command0_Click()
Dim Filepath As String
Filepath = "D:Xxxx bl_Impts_main.xlsx"
If FileExist(Filepath) Then
DoCmd.TransferSpreadsheet acImport, , "MainImportsfromxls", Filepath, True
[Code] .....
Code Source : [URL]
View 6 Replies
View Related
Jan 13, 2014
i have a form to enter a new client if the client is existed then a message box appear and tell me that this client is existed and his number id is ## with two buttons yes and no.if i click yes i should go to a report that contains information about that user and this report take his data from a query
new client save code
---------------------
Dim MSG As Integer
Dim ExistentID As Long
ExistentID = Nz(DLookup("P_ID", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N"), 0)
If ExistentID > 0 Then
[code]....
when i click yes the report should open with the existent id but he open a input box to ente an id and his title is tbl_personal_information.PID it's the ID field that it's named P_ID in the query
View 4 Replies
View Related
Aug 26, 2013
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
[code]...
View 3 Replies
View Related
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
Mar 21, 2005
I have a field that requires a user to put a date into. my dates are in the format 20040203
now i wantt to make sure the user enters a date greater then 2004?
i was doing this > 2004 in the validate property with a message box saying enter a date greater then 2004
but giving me an error when their is 20050203 the message box pops up? what am i missing or how should i handle this?
thanks pete
View 1 Replies
View Related
Sep 1, 2014
Code:
Private Sub Consolidate_Click()
Dim temp As Variant
Excel.Application.Visible = True
temp = Dir(CurrentProject.Path & "Inputs")
Do While temp <> vbNullString
[Code] ....
From the second iteration its not picking the error.
View 5 Replies
View Related
Nov 22, 2007
Dear all,
I have a lot of data already in my database and i need to validate to ensure that Various fields are populated correctly. how can i do this please?
thanks.
View 1 Replies
View Related
