How do I loop using multiple conditions for a submit button?

Question

0.00/5 (No votes)

See more:

I am writing a function that checks if the amount paid is equal or more to a number of items selected if they are checked. For this example there are 10 items with 10 check boxes. I could check one or more boxes in any other.If an item or more meets the condition it is cleared otherwise it remains.

What I have tried:

Public Sub processItem1()

Dim db As DAO.Database
Dim pr As DAO.Recordset, so As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String

Set db = CurrentDb

    strSQL1 = "SELECT * FROM PharmSales WHERE PharmSalesID= (SELECT MAX(PharmSalesID) FROM PharmSales WHERE HospitalNo='" & Me.txtRegNo & "' And TDate = #" &    Format(Me.txtTDate, "M\/dd\/yyyy") & "# AND SalesItem1 = '" & Me.txtSalesItem1 & "')"
    strSQL2 = "SELECT * FROM tblItem WHERE ItemName = '" & Me.txtSalesItem1 & "'"
Set pr = db.OpenRecordset(strSQL1)
Set so = db.OpenRecordset(strSQL2)

With pr
If Not .BOF And Not .EOF Then  'Ensure that the recordset contains records
.MoveLast
.MoveFirst
 If .Updatable Then  'To ensure record is not locked by another user
 .Edit  'Must start an update with the edit statement
 ![DispQty1] = Nz(![DispQty1] + Me.txtSalesQty1.Value, 0)
  .Update
End If
End If

 pr.Close  'Make sure you close the recordset..
 Set pr = Nothing  '...and set it to nothing
Set db = Nothing
End With

With so
If Not .BOF And Not .EOF Then  'Ensure that the recordset contains records
.MoveLast
.MoveFirst
 If .Updatable Then  'To ensure record is not locked by another user
 .Edit  'Must start an update with the edit statement
![Stock_Out] = Nz(![Stock_Out] + Me.txtSalesQty1.Value, Me.txtSalesQty1.Value)
 ![SO_Date] = Me.txtTDate
  ![Stock_In] = Nz(![Stock_In] + 0, 0)
  .Update  'And finally we will need to confirm the update

End If
End If
 so.Close  'Make sure you close the recordset..
 ExitSub:
 Set so = Nothing  '...and set it to nothing
Set db = Nothing

   End With
    End Sub

Second Item:

Public Sub processItem2()

Dim db As DAO.Database
Dim pr As DAO.Recordset, so As DAO.Recordset
Dim strSQL1 As String
Dim strSQL2 As String

Set db = CurrentDb

    strSQL1 = "SELECT * FROM PharmSales WHERE PharmSalesID= (SELECT MAX(PharmSalesID) FROM PharmSales WHERE HospitalNo='" & Me.txtRegNo & "' And TDate = #" & Format(Me.txtTDate, "M\/dd\/yyyy") & "# AND SalesItem2 = '" & Me.txtSalesItem2 & "')"
    strSQL2 = "SELECT * FROM tblItem WHERE ItemName = '" & Me.txtSalesItem2 & "'"
Set pr = db.OpenRecordset(strSQL1)
Set so = db.OpenRecordset(strSQL2)

With pr
If Not .BOF And Not .EOF Then  'Ensure that the recordset contains records
.MoveLast
.MoveFirst
 If .Updatable Then  'To ensure record is not locked by another user
 .Edit  'Must start an update with the edit statement
 ![DispQty2] = Nz(![DispQty2] + Me.txtSalesQty2.Value, 0)
  .Update
End If
End If

 pr.Close  'Make sure you close the recordset..
 Set pr = Nothing  '...and set it to nothing
Set db = Nothing
End With

With so
If Not .BOF And Not .EOF Then  'Ensure that the recordset contains records
.MoveLast
.MoveFirst
 If .Updatable Then  'To ensure record is not locked by another user
 .Edit  'Must start an update with the edit statement
 ![Stock_Out] = Nz(![Stock_Out] + Me.txtSalesQty2.Value, Me.txtSalesQty2.Value)
 ![SO_Date] = Me.txtTDate
  ![Stock_In] = Nz(![Stock_In] + 0, 0)
  .Update  'And finally we will need to confirm the update
End If
End If
 so.Close  'Make sure you close the recordset..
 ExitSub:
 Set so = Nothing  '...and set it to nothing
Set db = Nothing

End With
  End Sub

Posted 24-Aug-17 23:01pm

Liberty Crown Infotech

Updated 25-Aug-17 11:55am

Afzaal Ahmad Zeeshan

v2

Add a Solution

Comments

OriginalGriff 25-Aug-17 5:16am

And?
What does it do that you didn't expect, or not do that you did?
What help do you need?

Richard Deeming 25-Aug-17 11:35am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2017-08-25T11:55:00

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]