Help ! Validation of text boxes.

[beanbeanbean]

hi can anyone help me with the validation.

i'm having trouble with the codes.

i am creating an insert form which inserts data into the datasheet. Thats where the problem comes. it only allows me to proceed if all the fields have something in them. anyone knows how to allow the process to continue even if some fields are left blank ?

Also can u guys help me and tell me what error is this ?

" Invalid SQL statement; expected 'DELETE', 'INSERT', "PROCEDURE', 'SELECT', or 'UPDATE'. "

It happens everytime when i try to enter the data without filling in all the fields. After that comes out, then only will my msg box appear. Why is that so ?


Thanks for the help in advance. I am really grateful.

I've attached the code i have for reference.




Private Sub Command29_Click()
'------------------------------------------
If (Text88.Value) = "" Then
MsgBox "Please enter Lan ID"
ElseIf Text106.Value = "" Then
MsgBox ("Please enter Status")

ElseIf Text90.Value = "" Then
MsgBox ("Please enter Staff ID")

ElseIf Text94.Value = "" Then
MsgBox ("Please enter Franchise")

ElseIf Text92.Value = "" Then
MsgBox ("Please enter Querier's Email Address")

ElseIf Text96.Value = "" Then
MsgBox ("Please enter Channel Received")

ElseIf Text98.Value = "" Then
MsgBox ("Please enter Product 1")

ElseIf Text104.Value = "" Then
MsgBox ("Please enter Customer IC 1")

ElseIf Text112.Value = "" Then
MsgBox ("Please enter Dollar Amount 1")

ElseIf Text118.Value = "" Then
MsgBox ("Please enter Transaction Date 1")

ElseIf Text124.Value = "" Then
MsgBox ("Please enter Date of Query")

ElseIf Text126.Value = "" Then
MsgBox ("Please enter Discrepancy")

ElseIf Text128.Value = "" Then
MsgBox ("Please enter Request")
ElseIf Text130.Value = "" Then
MsgBox ("Please enter Staff's Email Address")

End If
'----------------------------------------
'------------------------------------------
If IsNull(Text88.Value) Then
Text88.Value = ""
ElseIf IsNull(Text106.Value) Then
Text106.Value = ""

ElseIf IsNull(Text90.Value) Then
Text90.Value = ""

ElseIf IsNull(Text92.Value) Then
Text92.Value = ""

ElseIf IsNull(Text94.Value) Then
Text94.Value = ""

ElseIf IsNull(Text96.Value) Then
Text96.Value = ""

ElseIf IsNull(Text98.Value) Then
Text98.Value = ""

ElseIf IsNull(Text104.Value) Then
Text104.Value = ""

ElseIf IsNull(Text112.Value) Then
Text112.Value = ""

ElseIf IsNull(Text118.Value) Then
Text118.Value = ""

ElseIf IsNull(Text126.Value) Then
Text126.Value = ""

ElseIf IsNull(Text128.Value) Then
Text128.Value = ""

ElseIf IsNull(Text124.Value) Then
Text124.Value = ""

ElseIf IsNull(Text130.Value) Then
Text130.Value = ""

ElseIf IsNull(Text100.Value) Then
Text100.Value = ""

ElseIf IsNull(Text102.Value) Then
Text102.Value = ""

ElseIf IsNull(Text108.Value) Then
Text108.Value = ""

ElseIf IsNull(Text110.Value) Then
Text110.Value = ""

ElseIf IsNull(Text114.Value) Then
Text114.Value = ""
ElseIf IsNull(Text116.Value) Then
Text116.Value = ""

ElseIf IsNull(Text120.Value) Then
Text120.Value = ""
ElseIf IsNull(Text122.Value) Then
Text122.Value = ""
End If
'-----------------------------------------------

Dim sqlString As String
On Error GoTo Err_Topic_NotInList
DoCmd.RunSQL "Insert Into [Query Database] ([Status],[Lan ID],[Staff ID],[Querier Email Address],[Franchise],[Channel Received],[Product 1],[Product 2],[Product 3],[Customer IC 1],[Customer IC 2],[Customer IC 3],[Dollar Amount 1],[Dollar Amount 2],[Dollar Amount 3],[Transaction Date 1],[Transaction Date 2],[Transaction Date 3],[Discrepancy],[Request],[Date of Query],[Staff Email Address])Values ('" + Text106.Value + "','" + Text88.Value + "','" + Text90.Value + "','" + Text92.Value + "','" + Text94.Value + "','" + Text96.Value + "','" + Text98.Value + "','" + Text100.Value + "','" + Text102.Value + "','" + Text104.Value + "','" + Text108.Value + "','" + Text110.Value + "','" + Text112.Value + "', '" + Text114.Value + "', '" + Text116.Value + "', '" + Text118.Value + "', '" + Text120 + "', '" + Text122 + "', '" + Text126 + "', '" + Text128 + "', '" + Text124 + "', '" + Text130 + "')"
'-----------------------------------------------
'------------------AUDIT LOG-----------------------------------------------------------------------------------------
Open "C:\Documents and Settings\johnteo\Desktop\Audit Log.txt" For Append As #1
Write #1, "-------------------------------------------------------------------------------------"
Write #1, "A new file : " & Text88.Value & " has been inserted on " & Format(Now(), "m/d/yy h:m:s AMPM") & ""
Write #1,
Close #1
Exit Sub
'Else: Exit Sub
'-----------------------------------------------
Err_Topic_NotInList:
' An unexpected error occurred,
' display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress
' an error message and undo changes.
Response = acDataErrContinue
Exit Sub

'db.Execute (sqlString)
'sqlString = "Insert Into [Simple_Check] ( ,,)Values ('" + Pot_Owner_Text.Value + "','" + New_Pot_Owner_Text.Value + "','" + Problematic_text.Value + "',,,)"
'db.Execute (sqlString)
'End If
End Sub

 

[CyberLynx]

Hmmmm....Lots of Message Boxes.

Try this instead. Add the following to Tag property for each of the following Text Boxes listed below (do not put in Quotation marks):

Text88: Please enter Lan ID-CHK
Text90: Please enter Staff ID-CHK
Text94: Please enter Franchise-CHK
Text92: Please enter Querier's Email Address-CHK
Text96: Please enter Channel Received-CHK
Text98: Please enter Product 1-CHK
Text104: Please enter Customer IC 1-CHK
Text106: Please enter Status-CHK
Text112: Please enter Dollar Amount 1-CHK
Text118: Please enter Transaction Date 1-CHK
Text124: Please enter Date of Query-CHK
Text126: Please enter Discrepancy-CHK
Text128: Please enter Request-CHK
Text130: Please enter Staff's Email Address-CHK

Don't worry about the -CHK at the end, it's just used as a flag.

Now use the code below in your Command Button:

Private Sub Command29_Click()
   [COLOR="DarkGreen"]'Declare necessary Variables[/COLOR]
   Dim Ctrl As Control
   Dim Msg As String
   
   [COLOR="DarkGreen"]'Enumerate through the Forms' Controls to see if any of our Important
   'Text Boxes did not receive data. If so then read the message string
   'we stored in the Tag property and use it to inform the User. We used
   'the [B]-CHK[/B] flag at the end of the Tag string to inform this procedure
   'that of all the controls on Form to process for User Entry, we are
   'particularly interested in the ones with the [B]-CHK[/B] flag at the end
   'of the Tag string.[/COLOR]
   For Each Ctrl In Me.Controls
      [COLOR="DarkGreen"]'If the Control detected in form is a TextBox then...[/COLOR]
      If Ctrl.ControlType = acTextBox Then
         [COLOR="DarkGreen"]'If here is nothing in the TextBox detected and there is a Flag
         'located within the Tag property of that Text Box then....[/COLOR]
         If Nz(Ctrl, "") = "" And Right$(Ctrl.Tag, 4) = "-CHK" Then
            [COLOR="DarkGreen"]'Set the [B]Msg[/B] variable to the string contained within the [B]Tag[/B]
            'property (less the Flag)[/COLOR]
            Msg = Left$(Ctrl.Tag, Len(Ctrl.Tag) - 4)
            [COLOR="DarkGreen"]'Since one of our important Fields on form has not been filled
            'in let's just get outta this loop. No sense processing any further.[/COLOR]
            Exit For
         End If
      End If
   Next Ctrl
   
   [COLOR="DarkGreen"]'If a Message was created then that indicates that a Important Form Field 
   'contains no data. Inform the User, set focus onto the Text Box that needs
   'data and exit this event.[/COLOR]
   If Msg <> "" Then
      MsgBox Msg & " - This must be done before you can continue.", _
                   vbExclamation, "Insufficient Data Entry"
      Ctrl.SetFocus
      Exit Sub
   End If
   
   [COLOR="DarkGreen"]'If we made it this far then everything is OK. 
   'Declare a string Variable to hold our INSERT Query.[/COLOR]
   Dim sqlString As String

   [COLOR="DarkGreen"]'Build the INSERT Query....[/COLOR]
   sqlString = "INSERT INTO [Query Database] ([Status],[Lan ID],[Staff ID],[Querier Email Address],[Franchise],[Channel Received],[Product 1]," & _
               "[Product 2],[Product 3],[Customer IC 1],[Customer IC 2],[Customer IC 3],[Dollar Amount 1],[Dollar Amount 2],[Dollar Amount 3]," & _
               "[Transaction Date 1],[Transaction Date 2],[Transaction Date 3],[Discrepancy],[Request],[Date of Query],[Staff Email Address]) Values " & _
               "(" & IIf(Nz(Me.Text106.Value, "") = "", "NULL", "'" & Text106.Value & "'") & "," & IIf(Nz(Me.Text88.Value, "") = "", "NULL", "'" & Text88.Value & "'") & _
               "," & IIf(Nz(Me.Text90.Value, "") = "", "NULL", "'" & Text90.Value & "'") & "," & IIf(Nz(Me.Text92.Value, "") = "", "NULL", "'" & Text92.Value & "'") & _
               "," & IIf(Nz(Me.Text94.Value, "") = "", "NULL", "'" & Text94.Value & "'") & "," & IIf(Nz(Me.Text96.Value, "") = "", "NULL", "'" & Text96.Value & "'") & _
               "," & IIf(Nz(Me.Text98.Value, "") = "", "NULL", "'" & Text98.Value & "'") & "," & IIf(Nz(Me.Text100.Value, "") = "", "NULL", "'" & Text100.Value & "'") & _
               "," & IIf(Nz(Me.Text102.Value, "") = "", "NULL", "'" & Text102.Value & "'") & "," & IIf(Nz(Me.Text104.Value, "") = "", "NULL", "'" & Text104.Value & "'") & _
               "," & IIf(Nz(Me.Text108.Value, "") = "", "NULL", "'" & Text108.Value & "'") & "," & IIf(Nz(Me.Text110.Value, "") = "", "NULL", "'" & Text110.Value & "'") & _
               "," & IIf(Nz(Me.Text112.Value, "") = "", "NULL", "'" & Text112.Value & "'") & "," & IIf(Nz(Me.Text114.Value, "") = "", "NULL", "'" & Text114.Value & "'") & _
               "," & IIf(Nz(Me.Text116.Value, "") = "", "NULL", "'" & Text116.Value & "'") & "," & IIf(Nz(Me.Text118.Value, "") = "", "NULL", "'" & Text118.Value & "'") & _
               "," & IIf(Nz(Me.Text120.Value, "") = "", "NULL", "'" & Text120.Value & "'") & "," & IIf(Nz(Me.Text122.Value, "") = "", "NULL", "'" & Text122.Value & "'") & _
               "," & IIf(Nz(Me.Text126.Value, "") = "", "NULL", "'" & Text126.Value & "'") & "," & IIf(Nz(Me.Text128.Value, "") = "", "NULL", "'" & Text128.Value & "'") & _
               "," & IIf(Nz(Me.Text124.Value, "") = "", "NULL", "'" & Text124.Value & "'") & "," & IIf(Nz(Me.Text130.Value, "") = "", "NULL", "'" & Text1130.Value & "'") & ");"

   On Error GoTo Err_Topic_NotInList

  [COLOR="DarkGreen"] 'Turn OFF MS-Access Warnings. This prevents the annoying 
   'You are about to... message.[/COLOR]
   DoCmd.SetWarnings False
   [COLOR="DarkGreen"]'Fire the INSERT Query we just built.[/COLOR]
   DoCmd.RunSQL sqlString
   [COLOR="DarkGreen"]'Turn MS-Access Warnings back on again.[/COLOR]
   DoCmd.SetWarnings True

   [COLOR="DarkGreen"]'Log the Process to File (Your code)
   '-----------------------------------------------
   '------------------AUDIT LOG--------------------[/COLOR]
   Open "C:\Documents and Settings\johnteo\Desktop\Audit Log.txt" For Append As #1
      Write #1, "-------------------------------------------------------------------------------------"
      Write #1, "A new file : " & Nz(Me.Text88.Value, "NOTHING") & " has been inserted on " & Format(Now(), "m/d/yy h:m:s AMPM") & ""
      Write #1,
   Close #1

Exit_This_Routine:
   Exit Sub

Err_Topic_NotInList:
   [COLOR="DarkGreen"]' An unexpected error occurred,
   ' display the normal error message.[/COLOR]
   MsgBox Err.Number & " -- " & Err.Description
    Resume Exit_This_Routine
   Exit Sub

   [COLOR="DarkGreen"]'[B]I DONT KNOW WHAT YOU HAVE THIS HERE FOR?[/B]
   'db.Execute (sqlString)
   'sqlString = "Insert Into [Simple_Check] ( ,,)Values ('" + Pot_Owner_Text.Value + "','" + New_Pot_Owner_Text.Value + "','" + Problematic_text.Value + "',,,)"
   'db.Execute (sqlString)
   'End If[/COLOR]
End Sub

Notice that all the Message Boxes are gone?

Note: This code has not been tested.

.

 

[DCrake]

The naming of controls Text01, Text02,Combo33,Combo34, etc is very bad practice. Just remember someone else may need to read this code at a later date and how would you expect them to understand it if it was like it is now?

How would like it and how login would it take you to decipher it? It also helps when using VBA to read what is actually going on.

This is constructive criticism, not a dig at your programming skills, remember we all started in this fashion.

CodeMaster:

 

[beanbeanbean]

thank you cyberlynx. god bless u. i'll try it out first and let u know =)

no worries dcrake. (= thanks for trying to help me improve.

 

[beanbeanbean]

hi cyberlynx so sorry but i think i need your help again. seems like there's some problem. When i run the code below,
it would state - Compile Error: Invalid Next control variable reference.
the debugger then takes me to this part of the code.

Next Ctrl

so what i did was to add a Quotation mark like this

Next 'Ctrl and the error was solved.

did i do the right thing ?

but after that another problem surfaced.

There was another compile error which said- Compile error: Argument not optional.

it takes me to this line of the code.

DoCmd.SetWarnings = False

and again i add a quotation mark infront to cancel out the code from working.

and then a 3rd problem surfaced which was-

Run-time error '91': Object variable or With block variable not set

I tried searching around on the net and all it said was that i did not Set Crtl as smth.

Would you please help me once again ? Thanks =)

The code that i edited is below. I've changed the textboxes name to their respective names for easier references.


Private Sub Command29_Click()
'------------------------------------------
'Declare necessary Variables
Dim Ctrl As Control
Dim Msg As String

'Enumerate through the Forms' Controls to see if any of our Important
'Text Boxes did not receive data. If so then read the message string
'we stored in the Tag property and use it to inform the User. We used
'the -CHK flag at the end of the Tag string to inform this procedure
'that of all the controls on Form to process for User Entry, we are
'particularly interested in the ones with the -CHK flag at the end
'of the Tag string.

For Each Control In Me.Controls

'If the Control detected in form is a TextBox then...
If Ctrl.ControlType = acTextBox Then
'If here is nothing in the TextBox detected and there is a Flag
'located within the Tag property of that Text Box then....

If Nz(Ctrl, "") = "" And Right$(Ctrl.Tag, 4) = "-CHK" Then

'Set the Msg variable to the string contained within the Tag
'property (less the Flag)

Msg = Left$(Ctrl.Tag, Len(Ctrl.Tag) - 4)

'Since one of our important Fields on form has not been filled
'in let's just get outta this loop. No sense processing any further.
Exit For
End If
End If

Next Ctrl

'If a Message was created then that indicates that a Important Form Field
'contains no data. Inform the User, set focus onto the Text Box that needs
'data and exit this event.

If Msg <> "" Then
MsgBox Msg & " - This must be done before you can continue.", _
vbExclamation, "Insufficient Data Entry"
Ctrl.SetFocus
Exit Sub
End If

'If we made it this far then everything is OK.
'Declare a string Variable to hold our INSERT Query.

Dim sqlString As String

'Build the INSERT Query....
sqlString = "INSERT INTO [Query Database] ([Status],[Lan ID],[Staff ID],[Querier Email Address],[Franchise],[Channel Received],[Product 1]," & _
"[Product 2],[Product 3],[Customer IC 1],[Customer IC 2],[Customer IC 3],[Dollar Amount 1],[Dollar Amount 2],[Dollar Amount 3]," & _
"[Transaction Date 1],[Transaction Date 2],[Transaction Date 3],[Discrepancy],[Request],[Date of Query],[Staff Email Address]) Values " & _
"(" & IIf(Nz(Me.status.Value, "") = "", "NULL", "'" & status.Value & "'") & "," & IIf(Nz(Me.lanid.Value, "") = "", "NULL", "'" & lanid.Value & "'") & _
"," & IIf(Nz(Me.staffid.Value, "") = "", "NULL", "'" & staffid.Value & "'") & "," & IIf(Nz(Me.queryemail.Value, "") = "", "NULL", "'" & queryemail.Value & "'") & _
"," & IIf(Nz(Me.franchise.Value, "") = "", "NULL", "'" & franchise.Value & "'") & "," & IIf(Nz(Me.channel.Value, "") = "", "NULL", "'" & channel.Value & "'") & _
"," & IIf(Nz(Me.productone.Value, "") = "", "NULL", "'" & productone.Value & "'") & "," & IIf(Nz(Me.producttwo.Value, "") = "", "NULL", "'" & producttwo.Value & "'") & _
"," & IIf(Nz(Me.productthree.Value, "") = "", "NULL", "'" & productthree.Value & "'") & "," & IIf(Nz(Me.customerone.Value, "") = "", "NULL", "'" & customerone.Value & "'") & _
"," & IIf(Nz(Me.customertwo.Value, "") = "", "NULL", "'" & customertwo.Value & "'") & "," & IIf(Nz(Me.customerthree.Value, "") = "", "NULL", "'" & customerthree.Value & "'") & _
"," & IIf(Nz(Me.dollarone.Value, "") = "", "NULL", "'" & dollarone.Value & "'") & "," & IIf(Nz(Me.dollartwo.Value, "") = "", "NULL", "'" & dollartwo.Value & "'") & _
"," & IIf(Nz(Me.dollarthree.Value, "") = "", "NULL", "'" & dollarthree.Value & "'") & "," & IIf(Nz(Me.transactionone.Value, "") = "", "NULL", "'" & transactionone.Value & "'") & _
"," & IIf(Nz(Me.transactiontwo.Value, "") = "", "NULL", "'" & transactiontwo.Value & "'") & "," & IIf(Nz(Me.transactionthree.Value, "") = "", "NULL", "'" & transactionthree.Value & "'") & _
"," & IIf(Nz(Me.discrepancy.Value, "") = "", "NULL", "'" & discrepancy.Value & "'") & "," & IIf(Nz(Me.request.Value, "") = "", "NULL", "'" & request.Value & "'") & _
"," & IIf(Nz(Me.datequery.Value, "") = "", "NULL", "'" & datequery.Value & "'") & "," & IIf(Nz(Me.staffemail.Value, "") = "", "NULL", "'" & staffemail.Value & "'") & ");"

On Error GoTo Err_Topic_NotInList

'Turn OFF MS-Access Warnings. This prevents the annoying
'You are about to... message.

DoCmd.SetWarnings = False

'Fire the INSERT Query we just built.

DoCmd.RunSQL sqlString

'Turn MS-Access Warnings back on again.
'DoCmd.SetWarnings True
'Log the Process to File (Your code)

'-----------------------------------------------
'------------------AUDIT LOG--------------------
Open "C:\Documents and Settings\johnteo\Desktop\Audit Log.txt" For Append As #1
Write #1, "-------------------------------------------------------------------------------------"
Write #1, "A new file : " & Nz(Me.lanid.Value, "NOTHING") & " has been inserted on " & Format(Now(), "m/d/yy h:m:s AMPM") & ""
Write #1,
Close #1
Exit_This_Routine:
Exit Sub
Err_Topic_NotInList:
' An unexpected error occurred,
' display the normal error message.
MsgBox Err.Number & " -- " & Err.Description
Resume Exit_This_Routine
Exit Sub

End Sub

 

[boblarson]

Your "problem" with the Next Ctl is that you have this:

For Each Control In Me.Controls

instead of what you SHOULD have:

For Each Ctrl In Me.Controls

 

[CyberLynx]

That's right beanbeanbean, Bob hit it on the nail. The line:

For Each Control In Me.Controls

should be:

For Each Ctrl In Me.Controls

and for the:

DmCmd.SetWarnings = False

Well...there should be no equals ( = ) sign:

DmCmd.SetWarnings False


Air code....what can I say other than I should be a little more careful.

It is definitely nice that you renamed your Text Boxes. You can see that it is a lot easier to work with and at a glance you can see which Control in code pertains to which Text Box on Form.

.

 

[beanbeanbean]

omg thank you so much to the both of u ! u guys really took me to the next level.

btw is there anyway like for example. i have the other 4 textboxes which are named customertwo, product two, dollar two and transaction two. is there anyway i can link them up ? like if only the textbox named product two is filled and all the others are left blank, the data would not be inserted because the others are left blank.

cos for the current code i've made all the other data entry to be compusory but for the product two and three boxes they do not need to be filled in but if one of them is filled in the others must be filled in too before the data can be entered into the table.

i've tried if else functions but most of them could not work. =(

also is there a way to like add a system clock which takes the time off the com into the form ? its like a date/time stamp which actually tells me the time i entered the records into the table. i currently have an audit log which stores the time into a text file on the destop. i'm jus wondering it there's another way to do it.

thanks !

 

[CyberLynx]

The answer to both your questions is.....Yes you can.

btw is there anyway like for example. i have the other 4 textboxes which are named customertwo, product two, dollar two and transaction two. is there anyway i can link them up ? like if only the textbox named product two is filled and all the others are left blank, the data would not be inserted because the others are left blank.

cos for the current code i've made all the other data entry to be compusory but for the product two and three boxes they do not need to be filled in but if one of them is filled in the others must be filled in too before the data can be entered into the table.

The code below is the same code I provided earlier (with correctons of course) and it also included additional code so as to attempt to do just that. Keep in mind that I can not test this code withoutyou DB or a sample of it. The Code I added is in Blue:

Private Sub Command29_Click()
   'Declare necessary Variables
   Dim Ctrl As Control      'Hold the Control (Text Box, Combo Box, etc) were are working with.
   Dim Msg As String        'Hold the Message we will display if there is an issue.
   Dim CtrlNmeEx As String  'Used to hold the extension of the Control Name (One, Two, Three, etc)
   Dim CtrlNme As String    'Used to temporarily hold the name of the Control to work with.
   
   'Enumerate through the Forms' Controls to see if any of our Important
   'Text Boxes did not receive data. If so then read the message string
   'we stored in the Tag property and use it to inform the User. We used
   'the [B]-CHK[/B] flag at the end of the Tag string to inform this procedure
   'that of all the controls on Form to process for User Entry, we are
   'particularly interested in the ones with the [B]-CHK[/B] flag at the end
   'of the Tag string.
   For Each Ctrl In Me.Controls
      'If the Control detected in form is a TextBox then...
      If Ctrl.ControlType = acTextBox Then
         [COLOR="DarkGreen"]'Let's Hold the Control Name so that we don't have
         'to reference Ctrl.Name all the time.[/COLOR]
         [COLOR="Blue"]CtrlNme = Ctrl.Name[/COLOR]
         [COLOR="DarkGreen"]'If the word "product in the name. If so let's check it out...[/COLOR]
         [COLOR="Blue"]If InStr(LCase$(CtrlNme), "product") > 0 And Len(CtrlNme) > 10 Then[/COLOR]
            [COLOR="DarkGreen"]'Let's make sure it's the Text Box we want and
            'not a Label or sothing else...[/COLOR]
            [COLOR="Blue"]If LCase$(Left$(CtrlNme, 10)) = "product" Then[/COLOR]
              [COLOR="DarkGreen"]'It's got to be a "product one" or "product two" (ect) Text Box so
              'let's get the number string from the name (one, teo, three, etc)
              'and hold it within the CtrlNmeEx variable.[/COLOR]
              [COLOR="Blue"]CtrlNmeEx = Mid$(CtrlNme, InStr(CtrlNme, " " + 1))[/COLOR]
              [COLOR="DarkGreen"]'Now...let's see if the product field actually contains something.
              'If it doesn't why bother carring on.[/COLOR]
              [COLOR="Blue"]If Len(Nz(Ctrl.Value, "")) <> 0 Then[/COLOR]
                 [COLOR="DarkGreen"]'Product does indeed contain something so let's check the
                 'other fields that pertain to that product....do the Dollar
                 'and Tranaction fields contain anyting?[/COLOR]
                 [COLOR="Blue"]If (Len(Nz(Me.Controls("dollar " & CtrlNmeEx), "")) = 0) Or _
                    (Len(Nz(Me.Controls("transaction " & CtrlNmeEx), "")) = 0) Then[/COLOR]
                    [COLOR="DarkGreen"]'WHAT!..They Don't!....let's Cancel out of this process and
                    'inform the user.[/COLOR]
                    [COLOR="Blue"]Msg = "Because 'Product " & CtrlNmeEx & _
                          "' contains a value you must ensure that both" & vbCr & _
                          "the 'Dollar " & CtrlNmeEx & "' and 'Transaction " & _
                          CtrlNmeEx & "' fields also contain a" & vbCr & "value."
                     Exit For
                 End If
              End If
            End If[/COLOR]
         End If
         
         [COLOR="DarkGreen"]'If here is nothing in the TextBox detected and there is a Flag
         'located within the Tag property of that Text Box then....[/COLOR]
         If Nz(Ctrl, "") = "" And Right$(Ctrl.Tag, 4) = "-CHK" Then
            'Set the [B]Msg[/B] variable to the string contained within the [B]Tag[/B]
           [COLOR="DarkGreen"] 'property (less the Flag)[/COLOR]
            Msg = Left$(Ctrl.Tag, Len(Ctrl.Tag) - 4)
            [COLOR="DarkGreen"]'Since one of our important Fields on form has not been filled
            'in let's just get outta this loop. No sense processing any further.[/COLOR]
            Exit For
         End If
      End If
   Next Ctrl
   
   [COLOR="DarkGreen"]'If a Message was created then that indicates that a Important Form Field
   'contains no data. Inform the User, set focus onto the Text Box that needs
   'data and exit this event.[/COLOR]
   If Msg <> "" Then
      MsgBox Msg & " - This must be done before you can continue.", _
                   vbExclamation, "Insufficient Data Entry"
      Ctrl.SetFocus
      Exit Sub
   End If
   
[COLOR="DarkGreen"]   'If we made it this far then everything is OK.
   'Declare a string Variable to hold our INSERT Query.[/COLOR]
   Dim sqlString As String

   [COLOR="DarkGreen"]'Build the INSERT Query....[/COLOR]
   sqlString = "INSERT INTO [Query Database] ([Status],[Lan ID],[Staff ID],[Querier Email Address],[Franchise],[Channel Received],[Product 1]," & _
               "[Product 2],[Product 3],[Customer IC 1],[Customer IC 2],[Customer IC 3],[Dollar Amount 1],[Dollar Amount 2],[Dollar Amount 3]," & _
               "[Transaction Date 1],[Transaction Date 2],[Transaction Date 3],[Discrepancy],[Request],[Date of Query],[Staff Email Address]) Values " & _
               "(" & IIf(Nz(Me.Status.Value, "") = "", "NULL", "'" & Status.Value & "'") & "," & IIf(Nz(Me.lanid.Value, "") = "", "NULL", "'" & lanid.Value & "'") & _
               "," & IIf(Nz(Me.staffid.Value, "") = "", "NULL", "'" & staffid.Value & "'") & "," & IIf(Nz(Me.queryemail.Value, "") = "", "NULL", "'" & queryemail.Value & "'") & _
               "," & IIf(Nz(Me.franchise.Value, "") = "", "NULL", "'" & franchise.Value & "'") & "," & IIf(Nz(Me.channel.Value, "") = "", "NULL", "'" & channel.Value & "'") & _
               "," & IIf(Nz(Me.productone.Value, "") = "", "NULL", "'" & productone.Value & "'") & "," & IIf(Nz(Me.producttwo.Value, "") = "", "NULL", "'" & producttwo.Value & "'") & _
               "," & IIf(Nz(Me.productthree.Value, "") = "", "NULL", "'" & productthree.Value & "'") & "," & IIf(Nz(Me.customerone.Value, "") = "", "NULL", "'" & customerone.Value & "'") & _
               "," & IIf(Nz(Me.customertwo.Value, "") = "", "NULL", "'" & customertwo.Value & "'") & "," & IIf(Nz(Me.customerthree.Value, "") = "", "NULL", "'" & customerthree.Value & "'") & _
               "," & IIf(Nz(Me.dollarone.Value, "") = "", "NULL", "'" & dollarone.Value & "'") & "," & IIf(Nz(Me.dollartwo.Value, "") = "", "NULL", "'" & dollartwo.Value & "'") & _
               "," & IIf(Nz(Me.dollarthree.Value, "") = "", "NULL", "'" & dollarthree.Value & "'") & "," & IIf(Nz(Me.transactionone.Value, "") = "", "NULL", "'" & transactionone.Value & "'") & _
               "," & IIf(Nz(Me.transactiontwo.Value, "") = "", "NULL", "'" & transactiontwo.Value & "'") & "," & IIf(Nz(Me.transactionthree.Value, "") = "", "NULL", "'" & transactionthree.Value & "'") & _
               "," & IIf(Nz(Me.discrepancy.Value, "") = "", "NULL", "'" & discrepancy.Value & "'") & "," & IIf(Nz(Me.request.Value, "") = "", "NULL", "'" & request.Value & "'") & _
               "," & IIf(Nz(Me.datequery.Value, "") = "", "NULL", "'" & datequery.Value & "'") & "," & IIf(Nz(Me.staffemail.Value, "") = "", "NULL", "'" & staffemail.Value & "'") & ");"
   
   On Error GoTo Err_Topic_NotInList

[COLOR="DarkGreen"]   'Turn OFF MS-Access Warnings. This prevents the annoying
   'You are about to... message.[/COLOR]
   DoCmd.SetWarnings False
   [COLOR="DarkGreen"]'Fire the INSERT Query we just built.[/COLOR]
   DoCmd.RunSQL sqlString
   [COLOR="DarkGreen"]'Turn MS-Access Warnings back on again.[/COLOR]
   DoCmd.SetWarnings True

   [COLOR="DarkGreen"]'Log the Process to File (Your code)
   '-----------------------------------------------
   '------------------AUDIT LOG--------------------[/COLOR]
   Open "C:\Documents and Settings\johnteo\Desktop\Audit Log.txt" For Append As #1
      Write #1, "-------------------------------------------------------------------------------------"
      Write #1, "A new file : " & Nz(Me.lanid.Value, "NOTHING") & " has been inserted on " & Format(Now(), "m/d/yy h:m:s AMPM") & ""
      Write #1,
   Close #1

Exit_This_Routine:
   Set Ctrl = Nothing
   Exit Sub

Err_Topic_NotInList:
   [COLOR="DarkGreen"]'An unexpected error occurred,
   'display the normal error message.[/COLOR]
   MsgBox Err.Number & " -- " & Err.Description
    Resume Exit_This_Routine
End Sub

The added code above assumes that the Form Control names are indeed product one (or two whatever), dollar one (or two whatever), and transaction one (or two whatever) and that all these control names are in lower case.

The number string is irrelevant that is after product because the code tries to pull the string and utilize it with the other two Controls.

If there is a Form Text Bix Control named product one then there must also be Text Box Controls on Form named dollar one and transaction one. On the same hand, if there is a Text Box Control on Form named product two the there must also be two Text Box Controls on Form named dollar two and transaction two and so forth.

also is there a way to like add a system clock which takes the time off the com into the form ? its like a date/time stamp which actually tells me the time i entered the records into the table. i currently have an audit log which stores the time into a text file on the destop. i'm jus wondering it there's another way to do it.

I'm not sure why you want to do this and how it pertains to your Text File log file since you already have a mechanism in place to place the current Date and Time the record was created. But if you just simply like to have a Date/Time display on your Form then you can do it this way:

- First, place a Text Box In the Form. Name it txtDTBox;

- Place this line of code into the Form's OnOpen event: Me.TimerInterval = 1000 ' 1 second

- Place this line of code into the Form's OnTimer event: Me.txtDTBox = Now()

- And finaly, place this line into the Form's OnUnload event: Me.TimerInterval = 0

Run your Form.

I personally prefer to keep my audit logs within a Table. This way you can also generate Audit Reports without the need to read a File and you can automatically trim the table if you like when it gets to big. You can trim a text file as well but I think it's just nicer to have it all under one roof so to speak. Here is how you can make a simple one:

- Create a Table and prepare to name it: AuditLog

- Add these Fields to the Table:

fldLogID (AutoNumber - Private Key) - Caption: Log ID
fldLogDate (Date/Time) - Caption: Log Date
fldRecordID (Number - Long Integer) - Caption: Record Created
fldCretatedBy (Text - 50) - Caption: Created By
fldFromCompter (Text - 50) - Caption: From Computer

- Save te Table. Be sure to name it: AuditLog

- Replace the Code where you append to the Text file on disk with this:

[COLOR="DarkGreen"]'Log Process to the AuditLog Table...
'Build our INSERT Query;[/COLOR]
sqlString = "INSERT INTO AuditLog (fldLogDate,fldRecordID,fldCretatedBy,fldFromCompter) VALUES " & _
            "(#" & Now() & "#," & Nz(Me.lanid.Value, 0) & ",'" & Environ("USERNAME") & "','" & _
            Environ("COMPUTERNAME") & "');"
   
[COLOR="DarkGreen"]'Turn OFF MS-Access Warnings. This prevents the annoying
'You are about to... message.[/COLOR]
DoCmd.SetWarnings False

[COLOR="DarkGreen"]'Fire the INSERT Query we just built.[/COLOR]
DoCmd.RunSQL sqlString

[COLOR="DarkGreen"]'Turn MS-Access Warnings back on again.[/COLOR]
DoCmd.SetWarnings True

If you have done this then what will happen is the record will be Inserted to Table and the AuditLog Table will have a Record added to it indicating the fact.

If you have a Microsoft DAO 3.x Object Library referenced in your Database then you can get rid of the:

Docmd.SetWarnings False
DoCmd.RunSQL strString
DoCmd.SetWarnings True

and just have this instead:

CurrentDb.Execute strString, dbFailOnError

There, that should keep you busy for a while

.

 

[beanbeanbean]

thanks for your audit log suggestion ! it was just what i needed !

would just like to ask. that i cant seem to get the code working.

i've tried playing around with the values by changing them to less than 5 but it doesn't work.

The whole form works. its just that when i enter values in the product two text box. it would not prompt saying that transaction two and dollor two needed to be inserted.

it would jus say "data entered" after i hit the submit button which is to submit the values to the table. this is a prompt that i added.

would the problem lie in the name of my textbox ? it was called producttwo (without a space). i tried running the code but there was no effect. then i tried renaming it product two (with a space) and edited everything in the code but it still wont work.


is it possible to restrict this code to just all the two's and three's. i've made the one's compusory to be entered so its ok.


Thanks ! The code is below. sorry its a bit messy as i do not know how u actually put the code in such a nice format like u did.

i've attached my database to my post. its in the 2003 version. god bless u =)



Private Sub Command29_Click()
'------------------------------------------


'Declare necessary Variables
Dim Ctrl As Control
'Hold the Control (Text Box, Combo Box, etc) were are working with.
Dim Msg As String
'Hold the Message we will display if there is an issue.
Dim CtrlNmeEx As String
'Used to hold the extension of the Control Name (One, Two, Three, etc)
Dim CtrlNme As String
'Used to temporarily hold the name of the Control to work with.

'Enumerate through the Forms' Controls to see if any of our Important
'Text Boxes did not receive data. If so then read the message string
'we stored in the Tag property and use it to inform the User. We used
'the -CHK flag at the end of the Tag string to inform this procedure
'that of all the controls on Form to process for User Entry, we are
'particularly interested in the ones with the -CHK flag at the end
'of the Tag string.
For Each Ctrl In Me.Controls

'If the Control detected in form is a TextBox then...
If Ctrl.ControlType = acTextBox Then

'Let's Hold the Control Name so that we don't have
'to reference Ctrl.Name all the time.
CtrlNme = Ctrl.name

'If the word "product in the name. If so let's check it out...
If InStr(LCase$(CtrlNme), "product") > 0 And Len(CtrlNme) > 10 Then

'Let's make sure it's the Text Box we want and
'not a Label or sothing else...
If LCase$(Left$(CtrlNme, 10)) = "product" Then

'It's got to be a "product one" or "product two" (ect) Text Box so
'let's get the number string from the name (one, two, three, etc)
'and hold it within the CtrlNmeEx variable.
CtrlNmeEx = Mid$(CtrlNme, InStr(CtrlNme, " " + 1))

'Now...let's see if the product field actually contains something.
'If it doesn't why bother carring on.
If Len(Nz(Ctrl.Value, "")) <> 0 Then

'Product does indeed contain something so let's check the
'other fields that pertain to that product....do the Dollar
'and Tranaction fields contain anyting?
If (Len(Nz(Me.Controls("dollar " & CtrlNmeEx), "")) = 0) Or _
(Len(Nz(Me.Controls("transaction " & CtrlNmeEx), "")) = 0) Then

'WHAT!..They Don't!....let's Cancel out of this process and
'inform the user.
Msg = "Because 'Product " & CtrlNmeEx & _
"' contains a value you must ensure that both" & vbCr & _
"the 'Dollar " & CtrlNmeEx & "' and 'Transaction " & _
CtrlNmeEx & "' fields also contain a" & vbCr & "value."
Exit For
End If
End If
End If
End If


'If here is nothing in the TextBox detected and there is a Flag
'located within the Tag property of that Text Box then....
If Nz(Ctrl, "") = "" And Right$(Ctrl.Tag, 4) = "-CHK" Then

'Set the Msg variable to the string contained within the Tag
'property (less the Flag)
Msg = Left$(Ctrl.Tag, Len(Ctrl.Tag) - 4)

'Since one of our important Fields on form has not been filled
'in let's just get outta this loop. No sense processing any further.
Exit For
End If
End If
Next Ctrl

'If a Message was created then that indicates that a Important Form Field
'contains no data. Inform the User, set focus onto the Text Box that needs
'data and exit this event.
If Msg <> "" Then
MsgBox Msg & " - This must be done before you can continue.", _
vbExclamation, "Insufficient Data Entry"
Ctrl.SetFocus
Exit Sub
End If

'If we made it this far then everything is OK.
'Declare a string Variable to hold our INSERT Query.
Dim sqlString As String

'Build the INSERT Query....
sqlString = "INSERT INTO [Query Database] ([Status],[Querier Lan ID],[Querier Staff ID],[Querier Email Address],[Franchise],[Channel Received],[Product 1]," & _
"[Product 2],[Product 3],[Customer IC 1],[Customer IC 2],[Customer IC 3],[Dollar Amount 1],[Dollar Amount 2],[Dollar Amount 3]," & _
"[Transaction Date 1],[Transaction Date 2],[Transaction Date 3],[Discrepancy],[Request],[Date of Query],[Staff Email Address]) Values " & _
"(" & IIf(Nz(Me.status.Value, "") = "", "NULL", "'" & status.Value & "'") & "," & IIf(Nz(Me.lanid.Value, "") = "", "NULL", "'" & lanid.Value & "'") & _
"," & IIf(Nz(Me.staffid.Value, "") = "", "NULL", "'" & staffid.Value & "'") & "," & IIf(Nz(Me.queryemail.Value, "") = "", "NULL", "'" & queryemail.Value & "'") & _
"," & IIf(Nz(Me.franchise.Value, "") = "", "NULL", "'" & franchise.Value & "'") & "," & IIf(Nz(Me.channel.Value, "") = "", "NULL", "'" & channel.Value & "'") & _
"," & IIf(Nz(Me.product_one.Value, "") = "", "NULL", "'" & product_one.Value & "'") & "," & IIf(Nz(Me.product_two.Value, "") = "", "NULL", "'" & product_two.Value & "'") & _
"," & IIf(Nz(Me.product_three.Value, "") = "", "NULL", "'" & product_three.Value & "'") & "," & IIf(Nz(Me.customer_one.Value, "") = "", "NULL", "'" & customer_one.Value & "'") & _
"," & IIf(Nz(Me.customer_two.Value, "") = "", "NULL", "'" & customer_two.Value & "'") & "," & IIf(Nz(Me.customer_three.Value, "") = "", "NULL", "'" & customer_three.Value & "'") & _
"," & IIf(Nz(Me.dollar_one.Value, "") = "", "NULL", "'" & dollar_one.Value & "'") & "," & IIf(Nz(Me.dollar_two.Value, "") = "", "NULL", "'" & dollar_two.Value & "'") & _
"," & IIf(Nz(Me.dollar_three.Value, "") = "", "NULL", "'" & dollar_three.Value & "'") & "," & IIf(Nz(Me.transaction_one.Value, "") = "", "NULL", "'" & transaction_one.Value & "'") & _
"," & IIf(Nz(Me.transaction_two.Value, "") = "", "NULL", "'" & transaction_two.Value & "'") & "," & IIf(Nz(Me.transaction_three.Value, "") = "", "NULL", "'" & transaction_three.Value & "'") & _
"," & IIf(Nz(Me.discrepancy.Value, "") = "", "NULL", "'" & discrepancy.Value & "'") & "," & IIf(Nz(Me.request.Value, "") = "", "NULL", "'" & request.Value & "'") & _
"," & IIf(Nz(Me.datequery.Value, "") = "", "NULL", "'" & datequery.Value & "'") & "," & IIf(Nz(Me.staffemail.Value, "") = "", "NULL", "'" & staffemail.Value & "'") & ");"
MsgBox Msg & " Data has been entered.", _
vbExclamation, "Data Entered"

On Error GoTo Err_Topic_NotInList

'Turn OFF MS-Access Warnings. This prevents the annoying
'You are about to... message.
DoCmd.SetWarnings False

'Fire the INSERT Query we just built.
DoCmd.RunSQL sqlString

'Turn MS-Access Warnings back on again.
DoCmd.SetWarnings True

'Log Process to the AuditLog Table...
'Build our INSERT Query;
sqlString = "INSERT INTO AuditLog (fldLogDate,fldRecordID,fldCretatedBy,fldFromCompter) VALUES " & _
"(#" & Now() & "#," & Nz(Me.lanid.Value, 0) & ",'" & Environ("USERNAME") & "','" & _
Environ("COMPUTERNAME") & "');"

'Turn OFF MS-Access Warnings. This prevents the annoying
'You are about to... message.
DoCmd.SetWarnings False
'Fire the INSERT Query we just built.
DoCmd.RunSQL sqlString
'Turn MS-Access Warnings back on again.
DoCmd.SetWarnings True

Exit_This_Routine:
Exit Sub
Err_Topic_NotInList:
' An unexpected error occurred,
' display the normal error message.
MsgBox Err.Number & " -- " & Err.Description
Resume Exit_This_Routine
Exit Sub

End Sub

 

[beanbeanbean]

strangely enough there is also a prompt asking me to insert a parameter value. any idea what is that ? or how to get rid of it ?

 

[beanbeanbean]

strangely enough there is also a prompt asking me to insert a parameter value. any idea what is that ? or how to get rid of it ?

oh its ok i got rid of this error already !

 

[CyberLynx]

Your Message Box was set a little to early in Code. It was displaying before the record is actually saved to Table. Code stops running where a Message Box is displayed until the Message Box is closed. This works but if there was an error after the Message Box then basically....the Message Box lied. It should be the last thing done to basically indicate success. I moved it.

There was indeed a problem in code as to why it wouldn't pick up the Empty Dollar Two and Transaction Date Two. I have corrected that now.

Like I mentioned in my earlier post. It's a lot better if there is a sample DB to work with, that way code can be tested in the environment it is intended for.

Here is you Sample DB back to you:


.

 

[beanbeanbean]

thanks !!! i'll try to make dollar connected to product and the transaction too. but i was wondering... hmm

i did this:

Private Sub datequery_BeforeUpdate(Cancel As Integer)
Dim foo As String
foo = Me.datequery
If Len(foo) < 11 Then
MsgBox Msg & "Date not inserted in proper format. Please refer to example below textbox. - This must be done before you can continue.", _
vbExclamation, "Insufficient Data Entry"
Cancel = True
End If
End Sub


to prevent 11 characters or less to be entered into my date of query textbox. this means it's still a free text format. and i had to put labels to remind the users to actually type in that particular format. it there a way to make the date be displayed in 00 / 00 / 0000 format ?

i tried using input masks and the validation rules. they actually work only when enter the form. but then i look into my table the numbers are all jumbled up together without the /. this means they end up like this 00000000.

 

[CyberLynx]

First off.....

i'll try to make dollar connected to product and the transaction too.

The code I provided already takes care of that.


As for your date format...force the User to enter the Format you want by setting a Input Mask. Copy and Paste the following line into the Input Mask property for the Date of Query Text Box on Form:

00\ /\ >LLL\ /\ 0000;0;_

You can find the Input Mask property under the Data tab within the Properties Window.

You then no longer need the code you made. With this mask the User is forced to place in your Format. You can add this mask for all your Date enty Text Boxes on Form.

On another note: The Message box does not need the Msg variable unless the Msg variable actually contains something:

This:

MsgBox Msg & "Date not inserted in proper format. Please refer to example below textbox. - This must be done before you can continue.", _
vbExclamation, "Incorrect Data Entry"

should be either:

MsgBox "Date not inserted in proper format. Please refer to example below textbox. - This must be done before you can continue.", _
vbExclamation, "Incorrect Data Entry"

OR

Dim Msg As String
Msg = "Date not inserted in proper format. Please refer to example below textbox. - This must be done before you can continue."
MsgBox Msg, vbExclamation, "Incorrect Data Entry"

.

 

[beanbeanbean]

thanks bro the date work !

Quote:
First off.....

Quote:
i'll try to make dollar connected to product and the transaction too.
The code I provided already takes care of that.


Well actually it takes care of the product only. i'm trying to play around so that it would actually work for the others too.

like for example. if i do enter something in the transaction two, dollar two and customer 2 box and leave the product two box empty. i am able to submit they data.

Will running your code 3 times with the edited textbox solve it ?



Was wondering is there anyway to update the data besides going to the table and update it ?

like for example this whole data has to be changed and some fields cannot be left blank.

i tried changing the data at the table. it seems tht some fields can be left blank.



thanks alot!

 

[beanbeanbean]

hello ! was also jus wondering

is there a way to restrict the dates ?

like only allowing users to type up to 31 in the days part of the date and 12 in the months part ?

like e.g 30 / 11 / 2008

instead of 32 / 32 / 2008 which my current date does now.



and also i have created a new text box. is there way to make the date restrictable to a 21 day period every month ?

like the date entered can only be 1/1/2008 to 21/1/2008. and it will change according to the month's date ?

thank you once again !

 

[CyberLynx]

Bottom line.....everything you want to do can be done. The thing is...it's not so easy to explain everything you want within a single post. Solve one problem at a time.

Pick the first one you want to tackle

.

 

[beanbeanbean]

ok lets start with the search and update function then =). thanks. really appreciate you helping me !


Was wondering is there anyway to update the data besides going to the table and update it ?


like for example a whole entry of data has to be changed.


i tried changing the data at the table. it seems that some fields can be left blank which i do not want.



also i found a search this search example of a database which is the one i want. but i do not know how to implement it into my form. i plan to put the search and update together. in a new form.


i would like to search by querier lan id (textbox) , querier email address (textbox) , franchise (drop down), product (listbox) , customer ic (textbox) and the transaction date (textbox jus like the age greater than and age less than. but i'm trying to select one date to another date).

for example if i type in one and select any of the combo and listbox at the same time like the example it would show all the records from the table.



attached below are the example database i found and my updated database.

god bless.

 

[CyberLynx]

This is actually two items which can be implemented into a single Form. What you need is a Entry/Edit interface with a search mechanism.

When it comes to the interface, well, it's all a matter of how you want to present your data for entry or edit. There are so many ways this can be done. In my opinion, the best way to edit data by a User is to present that data the same way as when they entered it. To me tables are so bland, but fine if all you are going to do is enter numbers. When there are multiple options to choose from then tables are not the way to go....to easy to make mistakes by modifying data in the wrong record. Queries can eliminate such a problem by displaying only the record or records to edit but I think a well laid out Form interface is still the way to go.

When I get a moment, I will look at your Database and implement some concepts.

There are lots of Data Search examples within this forum. Enough so to get you started.

.