Validate one line in recordset?

**Homegrownandy** · 08-30-2017, 03:42 AM

I've encountered a design error in the following code that I've put together:

Code:

Private Sub Command51_Click()
    Dim rst As DAO.Recordset
   Set rst = Me.RecordsetClone
    rst.MoveLast
    rst.MoveFirst
    Do Until rst.EOF
   
'Check Quantity
If rst!Temp_Quantity > rst!remaining_quantity Then
   MsgBox "Value entered (" & rst!Temp_Quantity & ") exceeds the available quantity" & rst!remaining_quantity
   Exit Sub
   Else
 End If
' If IsNull(Inv_Quantity) = True Then
' MsgBox "please input a quantity"
' Exit Sub
 'Else
' End If
'Check document type selected
 If IsNull(DocTypeCMB) = True Then
 MsgBox "please select a document type"
 Exit Sub
 Else
 End If
 
 
  If Me.DocTypeCMB.Value = "2" Then
   If Me.Inv_Quantity > 0 Then
   MsgBox "Credit value must be less than 0"
   Exit Sub
   End If
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "append to invoice table for credit", , acReadOnly
 Else
 End If
 
  If Me.DocTypeCMB.Value = "1" Then
   If Me.Inv_Quantity < 0 Then
   MsgBox "Invoice value must be greater than 0"
   Exit Sub
   End If
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "append to invoice table", , acReadOnly
    DoCmd.OpenQuery "Update_issue_3"
    Else
 End If
 
 
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    MsgBox "Done"

Generally this works as expected. But when I'm dealing with multiple lines on one order it can fail if not all of them are used at once.

Code:

 If IsNull(Inv_Quantity) = True Then
MsgBox "please input a quantity"
Exit Sub
Else
End If

This line looks for an entry in every [inv_quantity] I'm looking for a better solution to check there is some data to be moved, so there must be at least one entry. But beyond that the others can be blank. Any ideas appreciated, even if its not VBA code.

**ranman256** · 08-30-2017, 05:32 AM

you should be using queries, not code.

**Homegrownandy** · 08-30-2017, 06:18 AM

how do you mean?

**Homegrownandy** · 08-30-2017, 06:25 AM

the query is set up to only move the info where its populated. But I want the message box to tell the user.

**ssanfu** · 08-30-2017, 06:51 PM

Generally this works as expected. But when I'm dealing with multiple lines on one order it can fail if not all of them are used at once.

Not sure what you are asking????

I modified your code a little...

Code:

Private Sub Command51_Click()
    Dim rst As DAO.Recordset

    Set rst = Me.RecordsetClone

     'check to see if records on record set
    If rst.BOF And rst.EOF Then
        'no records - tell them
        MsgBox "No records"
    Else
        rst.MoveLast
        '        MsgBox rst.RecordCount   '<- for testing
        rst.MoveFirst

        'loop through the record set
        Do Until rst.EOF

            'Check Quantity
            If rst!Temp_Quantity > rst!remaining_quantity Then
                MsgBox "Value entered (" & rst!Temp_Quantity & ") exceeds the available quantity " & rst!remaining_quantity    
                'I added a space after "exceeds the available quantity" to separate the text from the number
                Exit Do
            End If

            '   If IsNull(Inv_Quantity) = True Then
            '       MsgBox "please input a quantity"
            '       Exit Do    ' Exit Sub
            '   Else   '<<-- not needed
            '   End If

            'Check document type selected
            If IsNull(DocTypeCMB) = True Then
                MsgBox "please select a document type"
                Exit Do
            End If

            'what is the data type of Me.DocTypeCMB?
            ' is it a number 2 or a string "2"????
            Select Case Me.DocTypeCMB
                Case "1"
                    '       If Me.DocTypeCMB.Value = "1" Then
                    If Me.Inv_Quantity < 0 Then
                        MsgBox "Invoice value must be greater than 0"
                        Exit Do
                    End If
                    DoCmd.SetWarnings False
                    DoCmd.OpenQuery "append to invoice table", , acReadOnly
                    DoCmd.OpenQuery "Update_issue_3"
                    DoCmd.SetWarnings True  '<<-- ALWAYS turn on warnings after you turn them off
                    '            Else   '<<-- not needed
                    '       End If
                Case "2"
                    '  If Me.DocTypeCMB.Value = "2" Then
                    If Me.Inv_Quantity > 0 Then
                        MsgBox "Credit value must be less than 0"
                        Exit Do
                    End If

                    DoCmd.SetWarnings False
                    DoCmd.OpenQuery "append to invoice table for credit", , acReadOnly
                    DoCmd.SetWarnings True  '<<-- ALWAYS turn on warnings after you turn them off
                    '     Else   '<<-- not needed
                    '  End If
            End Select

            rst.MoveNext
        Loop
    End If   ' If rst.BOF And rst.EOF Then

    'clean up
    rst.Close
    Set rst = Nothing

    MsgBox "Done"
End Sub

I changed "Exit Sub" to "Exit Do" because you are inside of a "DO - LOOP" . Now the code will fall through to the clean up code and end gracefully.
With the "Exit Sub" lines, the record set is not closed properly.

What is the SQL for the queries "append to invoice table for credit" and "Update_issue_3"?? They are action queries???

(

spaces in names). I would use something like "AppendInvoiceCredit" or "Append_Invoice_Credit"...

And instead of setting warnings off, I would use code like

Code:

Dim dB as DAO.Database

Set dB = Currentdb
.
.other code
.
db.Execute "append to invoice table", dbFailOnError
dB.Esecute "Update_issue_3", dbFailOnError
.
. other code
.

'clean up

Set dB = Nothing

again, just the way I code.....

**John_G** · 08-30-2017, 08:45 PM

I'm not sure the logic in you code is correct. You create a recordset of all the records in the form (recordsetclone), and then you have a do loop to set through the recordset.

However, only ONE of all the validation checks have anything to do with the recordset (the one that checks quantity) - the others check entries in (I think) other form fields.

A statement like this:
If IsNull(Inv_Quantity) = True Then

even though it is inside the loop, is going to refer to the same record every time - the one form happens to be on.

Also, the commands to execute the queries are inside the loop, meaning they are executed multiple times. Is that really what you want to do?

**Homegrownandy** · 08-31-2017, 01:54 AM

Thanks for the input, that all makes sense. I'll use ssanfus code and make amendments. I might be over complicating this but its good practice using VBA. I'm wondering how to check that at least one (Inv_Quantity) textbox is populated.

Maybe loop through the recordset until one has a value.. Ill see what I can do.

**John_G** · 08-31-2017, 12:52 PM

I'm wondering how to check that at least one (Inv_Quantity) textbox is populated.

What is Inv_Quantity? You refer to it as a textbox, so I assume it is on the form. But if you want to check all its values (using your code), it has to be in the RecordSetClone, you have to use the rst! notation to reference it, and you have to use the name of the field in the form's record source table, not the name of the textbox.

Another way to check that at least one Inv_Quantity is present is to use the findfirst method on your recordset, and then use the NoMatch property to see if a record was found.
This assimes that Inv_Quantity is a field in the recordset:

Code:

rst.findfirst "Nz(Inv_Quantity,0) > 0"
if rst.nomatch then
  '
  ' No record was found with Inv_Quantity > 0
  '
  exit sub
endif

Validate one line in recordset?

Thread Tools

Validate one line in recordset?

Similar Threads

Access VBA to write column and row data into excel sheet line by line ?

How do I extend a INSERT INTO line of code to the 2nd, third and fourth line???

Access Form Update calculation Programming line by line

Use the result of a recordset without moving through it line by line?

how to validate the first line of address

Posting Permissions