Results 1 to 1 of 1
  1. #1
    RaMcHiP is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    4

    Changing Subform Information In Relation To Current Form

    Ok this is the problem that I am running into now. I finally got the code to work to change the cab# on the main form, but I have a subform that has multiple inventories of each of the vehicles. The information in those subforms are stored in a table called Vehicle Inventory with 2 columns that matter, inventoryID (primary key, autonumber) cab# (indexed, allow multiples). I want to find all of the records that have strOldCAB in the cab# column and replace it with strNewCAB. I tried this with forms but failed because when I did the "DoCmd.FindRecord strOldCAB" I believe it was searching the vehicle inventoryID column and not the cab# column. Below is the code that I failed with (all of the lines that are commented out are the missing link) is there any other way to do this?



    Code:
    Private Sub CAB_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim strNewCAB As String
    Dim strOldCAB As String
    Dim strSearch As String
     
        If EditCAB = False Then
            If MsgBox("Are You Sure You Would Like To Change This Cab number?", _
                      vbYesNo + vbQuestion + vbDefaultButton2, _
                      "Please Confirm:") = vbYes Then
                strOldCAB = CAB.Value
            EditCAB = True
                strNewCAB = InputBox("What is the new cab number?", "New Cab number")
     
                    If strNewCAB = "" Or strNewCAB = Empty Then
                        MsgBox "No Input Provided", vbInformation, "Required Data"
                        EditCAB = False
                        CAB.SetFocus
                        Me.Refresh
                        Exit Sub
                    End If
     
                    If strNewCAB > 999 Then
                        MsgBox "Sorry, This Cab Number Is Incorrect", _
                        vbOKOnly, "Important Information"
                        EditCAB = False
                        CAB.SetFocus
                        Me.Refresh
                        Exit Sub
                    End If
     
                    Me.Requery
                    DoCmd.ShowAllRecords
                    DoCmd.GoToControl ("CAB")
                    DoCmd.FindRecord strNewCAB
                    strSearch = CAB.Text
     
                        If strSearch = strNewCAB Then
                            Me.Requery
                            DoCmd.ShowAllRecords
                            DoCmd.GoToControl ("CAB")
                            DoCmd.FindRecord strOldCAB
                            MsgBox "" & strSearch & " Is Taken - Please Try Again.", _
                                    , "Important Information"
                            Exit Sub
                        Else
                            DoCmd.GoToControl ("CAB")
                            DoCmd.FindRecord strOldCAB
                            txtoldcab.Visible = True
                            txtoldcab.SetFocus
                            txtoldcab.Locked = False
                            txtoldcab.Value = strOldCAB
                            txtoldcab.Locked = True
                            CAB.SetFocus
                            txtoldcab.Visible = False
                            CAB.Value = strNewCAB
                            CAB.Locked = True
                            EditCAB = False
    '                        DoCmd.OpenForm ("vehicle inventory")
    '                        DoCmd.Requery
    '                        CAB.SetFocus
    '                        DoCmd.FindRecord "strOldCAB", acEntire, , , , acCurrent, True
    '                        strSearch = CAB.Value
    '                        Do
    '                            If strSearch = strOldCAB Then
    '                            CAB.SetFocus
    '                            CAB.Value = strNewCAB
    '                            DoCmd.RunCommand acCmdSaveRecord
    '                            DoCmd.Requery
    '                            CAB.SetFocus
    '                            DoCmd.FindRecord "strOldCAB", acEntire, , , , acCurrent, True
    '                            strSearch = CAB.Value
    '                            End If
    '                        Loop While strSearch = strOldCAB
    '                        DoCmd.Close acForm, "Vehicle Inventory"
                            Me.Requery
                            DoCmd.FindRecord strNewCAB
                            DoCmd.RunCommand acCmdSaveRecord
                            MsgBox "Cab #" & strOldCAB & " Has Been Changed To Cab #" & strNewCAB & "", _
                                    , "Important Information"
                        End If
            End If
     
        End If
    End Sub
    Last edited by RaMcHiP; 05-10-2009 at 01:45 AM. Reason: Made a little headway

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Moving Information from One Form to Another
    By bsharpbyoung in forum Forms
    Replies: 2
    Last Post: 03-22-2009, 09:37 PM
  2. Replies: 0
    Last Post: 11-11-2008, 07:15 AM
  3. Form that hides information
    By dromorkid in forum Forms
    Replies: 0
    Last Post: 11-04-2008, 11:25 AM
  4. Changing a date to the current year
    By fdnyfish in forum Access
    Replies: 1
    Last Post: 03-01-2008, 08:34 AM
  5. Replies: 1
    Last Post: 02-14-2007, 07:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums