We help IT Professionals succeed at work.

Using a Bound Form and VBA for Recording Handling

pwdells
pwdells asked
on
Hello,

I have run into a bit of a dilemma.  Usually, I avoid using forms with fields that are bound to fields in a table.  I started off with using VBA to create a record, insert a UserID and StatusID, then returen the primary key into a form field called REQID.  That would happen in the background.  

Then the user would fill in the rest of the fields that were date, double, and text datatypes.  I was running an UPDATE query to update the recenty created record, with the new entered info.  However, this form now needs to be used globally (around the world) - new requirement. I have been fighting with the European date and number formats for a week.  The only thing that works in both US and Euro environments is to just make the fields bound to the table.  (Which I don't want to do - but don't have time to fight it. - I am over deadline now.)

So, that is the back story.  This is what I am looking to do now.   After the user enters the request information and hits the Save button, VBA runs a check to make sure the fields are entered in the correct format and are not null.  The fields are now assigned to fields within the table.  But when I run the code to save, the data does not save into the table.  

I don't feel that this impossible.  I am just a bit overwhelmed with the constant return of failure.

Any ideas?
Comment
Watch Question

Commented:
I'm not sure I understand... When the fields are bound to a table the data is saved automatically.  You put your validation routines in the beforeupdate and beforeinsert events (before data is committed to the table) to prevent this from happening.

What exactly is happening?  Error message?
Hamed NasrRetired IT Professional

Commented:
If you can recreate the issue in a simple access database, attach to have a look at.
One table with 4 fields and a form will be enough.
MIS Liason
Most Valuable Expert 2012
Commented:
Like kmslogic, I am a bit confused...


<Usually, I avoid using forms with fields that are bound to fields in a table. >
Why...?
AFAICT what you are asking can be easily done if you used a bound form and avoided all this work of running update queries...

It is not clear what you ultimate question is here...?
You say that you have date issues, then you state that you have issues with your "Save code"
(Note that you did not post your Save button code for us to examine...)

As far as date issues are concerned, sometimes find it much easier to deal with Multiple date formats by simply breaking the date into three separate fields:
Year-Month-Date
...to match the universal, international date format (ISO 8601)
ex: 2011-09-15
see here:
http://en.wikipedia.org/wiki/ISO_8601

You can then concatenate the fields in any way you like, for presentation purposes

JeffCoachman

Author

Commented:
Okay I will do that for you guys.  Thank you.
Check and read below article on handling international dates from Allen Browne:

http://allenbrowne.com/ser-36.html

And this thread by Kaplan:
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=7#7

Hope this helps,
Daniel
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
ahh, i might be missing something here, but where is the code for the Save button and any other relevant code ?

Author

Commented:
Save Code here:

Private Sub cmdSave_Click()
    Dim blnTrip As Boolean
    Dim strUpdate As String
    Dim DB As database
    
    Dim FIN As String
    Dim LEN_FIN As Integer
    Dim INSTR_FIN As Integer
    Dim R_FIN As Integer
    Dim L_FIN As Integer
    Dim FINAL As Double
    
    Dim NEW_DATE As String
    Dim SQL_Date As String
    Dim Total_Date As String
    Dim Crit_Date As String
    Dim Req_Date As String
    
    Set DB = CurrentDb
    
    blnTrip = False
    
    'check quantities
    
        'TSW
            If IsNull(Me.txtFIN_TSW.Value) = True Then
                blnTrip = True
            End If
            
        'Usability
            If IsNull(Me.txtFIN_Usability.Value) = True Then
                blnTrip = True
            End If
            
        'TOtal
            If IsNull(Me.txtFIN_Total.Value) = True Then
                blnTrip = True
            Else
                                
                'FIN = Trim(Str(Me.txtFIN_Total.Value))
                'LEN_FIN = Len(FIN)
                'INSTR_FIN = InStr(1, FIN, ",")
                'For Euro MS Environment
                'If INSTR_FIN > 0 Then
                '    L_FIN = Left(FIN, INSTR_FIN - 1)
                '    R_FIN = Right(FIN, LEN_FIN - INSTR_FIN)
                '
                '    FIN = L_FIN & "." & R_FIN
                '    FINAL = CDbl(FIN)
               '
               '     Me.txtFIN_Total.Value = FINAL
               ' Else
               '     'do nothing - US MS Environment
                'End If
            End If
            
        'Critical
            If IsNull(Me.txtFIN_Critical.Value) = True Then
                blnTrip = True
            Else
                FIN = Trim(Str(Me.txtFIN_Critical.Value))
                LEN_FIN = Len(FIN)
                INSTR_FIN = InStr(1, FIN, ",")
               'For Euro MS Environment
                If INSTR_FIN > 0 Then
                    L_FIN = Left(FIN, INSTR_FIN - 1)
                    R_FIN = Right(FIN, LEN_FIN - INSTR_FIN)
                                
                    FIN = L_FIN & "." & R_FIN
                    FINAL = CDbl(FIN)
               
                    Me.txtFIN_Critical.Value = FINAL
                Else
                'do nothing - US MS Environment
                End If
            End If
            
        'NET
            If IsNull(Me.txtFIN_Net.Value) = True Then
                blnTrip = True
            Else
                FIN = Trim(Str(Me.txtFIN_Net.Value))
                LEN_FIN = Len(FIN)
                INSTR_FIN = InStr(1, FIN, ",")
                'For Euro MS Environment
                If INSTR_FIN > 0 Then
                    L_FIN = Left(FIN, INSTR_FIN - 1)
                    R_FIN = Right(FIN, LEN_FIN - INSTR_FIN)
                
                    FIN = L_FIN & "." & R_FIN
                    FINAL = CDbl(FIN)
               
                    Me.txtFIN_Net.Value = FINAL
                Else
                    'do nothing - US MS Environment
                End If
            End If
                    
    'check dates
        
        'Request
            If IsNull(Me.txtReqDate.Value) = True Then
                blnTrip = True
            Else
            NEW_DATE = Trim(CStr(Me.txtReqDate.Value))
            If IsDate(NEW_DATE) Then
                If DateValue(NEW_DATE) = NEW_DATE Then
                    
                    SQL_Date = Format$(NEW_DATE, "\#mm\/dd\/yyyy\#")
                    Req_Date = SQL_Date
                    
                End If
            End If
                    
            '    Debug.Print "*** REQUEST DATE *** "
            '    Debug.Print "LENGTH: " & Len(NEW_DATE)
             ''   Debug.Print "ENTERED DATE: " & NEW_DATE
              '  If Len(NEW_DATE) < 10 Then
              '      If mid(NEW_DATE, 2, 1) = "/" Then
              '          NEW_DATE = "0" & NEW_DATE
              '          Debug.Print "first set missing 0 now: " & NEW_DATE
              '      End If
                    
               '     If mid(NEW_DATE, 5, 1) = "/" Then
               '         NEW_DATE = Left(NEW_DATE, 3) & _
               '             "0" & Right(NEW_DATE, 6)
                ''        Debug.Print "second set missing 0 now: " & NEW_DATE
                '    End If
                'End If
                'Me.txtReqDate.Value = mid(NEW_DATE, 4, 2) & "/" & _
                 '   Left(NEW_DATE, 2) & "/" & _
                 '   Right(NEW_DATE, 4)
                'Debug.Print "Euro Request Value: " & Me.txtReqDate.Value
                'Debug.Print "*** END REQUEST VALUE ***"
            End If
            
        'Critical
            If IsNull(Me.txtCritDate.Value) = True Then
                blnTrip = True
            Else
                NEW_DATE = Trim(CStr(Me.txtCritDate.Value))
                If IsDate(NEW_DATE) Then
                    If DateValue(NEW_DATE) = NEW_DATE Then
                        SQL_Date = Format$(NEW_DATE, "\#mm\/dd\/yyyy\#")
                        Crit_Date = SQL_Date
                    End If
                End If
                
                'Debug.Print "*** Critical DATE *** "
                'NEW_DATE = Trim(CStr(Me.txtCritDate.Value))
                'Debug.Print "LENGTH: " & Len(NEW_DATE)
                'Debug.Print "ENTERED DATE: " & NEW_DATE
                'If Len(NEW_DATE) < 10 Then
                '    If mid(NEW_DATE, 2, 1) = "/" Then
                '        NEW_DATE = "0" & NEW_DATE
                '        Debug.Print "first set missing 0 now: " & NEW_DATE
                '    End If
                '
                '    If mid(NEW_DATE, 5, 1) = "/" Then
                '        NEW_DATE = Left(NEW_DATE, 3) & _
                '            "0" & Right(NEW_DATE, 6)
                '        Debug.Print "second set missing 0 now: " & NEW_DATE
                '    End If
                'End If
                'Me.txtCritDate.Value = mid(NEW_DATE, 4, 2) & "/" & _
                '    Left(NEW_DATE, 2) & "/" & _
                '    Right(NEW_DATE, 4)
                'Debug.Print "Euro Critical Value: " & Me.txtCritDate.Value
               '
            End If
            
        'Total
            If IsNull(Me.txtTotalDate.Value) = True Then
                blnTrip = True
            Else
               
                NEW_DATE = Trim(CStr(Me.txtTotalDate.Value))
                If IsDate(NEW_DATE) Then
                    If DateValue(NEW_DATE) = NEW_DATE Then
                        SQL_Date = Format$(NEW_DATE, "\#mm\/dd\/yyyy\#")
                        Total_Date = SQL_Date
                    End If
                End If
               ' Debug.Print "*** Total DATE *** "
               ' NEW_DATE = Trim(CStr(Me.txtTotalDate.Value))
               '' Debug.Print "LENGTH: " & Len(NEW_DATE)
                'Debug.Print "ENTERED DATE: " & NEW_DATE
                'If Len(NEW_DATE) < 10 Then
                '    If mid(NEW_DATE, 2, 1) = "/" Then
                '        NEW_DATE = "0" & NEW_DATE
                '        Debug.Print "first set missing 0 now: " & NEW_DATE
                '    End If
                '
                '    If mid(NEW_DATE, 5, 1) = "/" Then
                '        NEW_DATE = Left(NEW_DATE, 3) & _
                '            "0" & Right(NEW_DATE, 6)
                '        Debug.Print "second set missing 0 now: " & NEW_DATE
                '    End If
                'End If
                'Me.txtTotalDate.Value = mid(NEW_DATE, 4, 2) & "/" & _
                '    Left(NEW_DATE, 2) & "/" & _
                '    Right(NEW_DATE, 4)
                'Debug.Print "Euro Total Value: " & Me.txtTotalDate.Value
            End If
            
    If blnTrip = True Then
        MsgBox "Make sure all quantity and dates fields are completed before proceeding."
    Else
        Debug.Print "Total date: " & Total_Date
        Debug.Print "Critical Date: " & Crit_Date
        Debug.Print "Req Date: " & Req_Date
        'run query
        strUpdate = _
            "UPDATE Request " & _
            "SET Request.Req_Qty = " & CDbl(Me.txtFIN_Net.Value) & ", " & _
                "Request.Crit_Qty = " & CDbl(Me.txtFIN_Critical.Value) & ", " & _
                "Request.Req_Dt = " & Req_Date & ", " & _
                "Request.Crit_Dt = " & Crit_Date & ", " & _
                "Request.Total_Dt = " & Total_Date & ", " & _
                "Request.Remarks = " & Chr(34) & Nz(Me.memRemarks, "-") & Chr(34) & ", " & _
                "Request.Usability = " & Me.txtFIN_Usability.Value & ", " & _
                "Request.TSW = " & Me.txtFIN_TSW.Value & " " & _
            "WHERE ID = " & REQID
        Debug.Print strUpdate
         DB.Execute (strUpdate)
        
         DB.Execute ("UPDATE tbx_ChangeControl SET Switch = False Where FORMID = 22")
         
        Dim str_NewElement As String
        
        str_NewElement = _
            "INSERT INTO ReqElements ( REQID, Type, Quantity, TypeDate, Active ) " & _
            "SELECT " & REQID & ", TRUE, " & _
                CDbl(Me.txtFIN_Critical.Value) & ", " & _
                "#" & Me.txtCritDate.Value & "#, TRUE"

        Debug.Print (str_NewElement)
        
        DB.Execute (str_NewElement)
         
        str_NewElement = _
            "INSERT INTO ReqElements ( REQID, Type, Quantity, TypeDate, Active ) " & _
            "SELECT " & REQID & ", FALSE, " & _
                CDbl(Me.txtFIN_Net.Value) & ", " & _
                "#" & Me.txtTotalDate.Value & "#, TRUE"

        Debug.Print (str_NewElement)
        
        DB.Execute (str_NewElement)
        
        DoCmd.GoToRecord , , acFirst
        DoCmd.GoToRecord , , acLast
        
        Call ClearReqFields
         
        Me.lstSearch.Enabled = True
        Me.lstSearch.RowSource = ""
        Me.boxScreen.Visible = True
        Me.boxScreen.Top = 0.0833 * 1440
        Me.boxScreen.Height = 6.375 * 1440
        Me.lstCoE.Visible = False
        Me.lstResults_CoE.Visible = False

    End If
End Sub

Open in new window

Author

Commented:
I just bound a table to the form - even though I disburse data between 3 tables.  I made it work.

The only way that this will work in the EUro and US environments, if I use a bound form.  So, after saving the data in the record that is bound to the form, I run VBA behind the scenes to populate the other two tables using INSERT INTO SQL.

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for pwdells's comment http:/Q_27480612.html#37322812

for the following reason:

Had to do a lot of trial and error using a VPN connection with a computer Europe. &nbsp;It was painstakingly slow -- but I figured it out after sleepless nights.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I just bound a table to the form >

That is what I suggested:
<what you are asking can be easily done if you used a bound form>
...in 37244962