Results 1 to 5 of 5

Thread: Refresh

  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Refresh

    Hi All,

    I'm having a problem after conversion of an Access 2.0 database into Access 2K
    After a selection is made by means of a combobox the underlying data is not refreshed.
    When i use the following command's, Remove Filter / Filter by Form / Apply Filter
    after a selection the data is refreshed.

    I assume the problem is the vba code. As i'm not very familiar with vba code i'm asking for your assistance.

    btw
    The data is refreshed when i open the database in Access 97. However that's not the intention.

    Attached is the form's vba code.

    TIA
    Joop

    As i do not see the attachment code as follows
    Option Compare Database 'Use database order for string comparisons


    Private Sub ButtonAddOperator_Click()
    On Error GoTo ButtonAddOperator_Click_Err:

    DoCmd.OpenForm "frm_Operator", , , A_ADD

    Exit Sub

    ButtonAddOperator_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonAddOwner1_Click()
    On Error GoTo Err_ButtonAddOwner1_Click

    DoCmd.OpenForm "frm_owner", , , A_ADD

    Exit_ButtonAddOwner1_Click:
    Exit Sub

    Err_ButtonAddOwner1_Click:
    MsgBox Error$
    Resume Exit_ButtonAddOwner1_Click

    End Sub

    Private Sub ButtonCasualtyHistor_Click()
    On Error GoTo Err_ButtonCasualtyHistor_Click

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_CasualtyInformation"
    LinkCriteria = "[LRND] = Forms![frm_VesselInformation]![LRN] "

    If IsNull(DLookup("[LRND]", "tbl_CAS_CasualtyData", "[lrnd]=[Forms]![frm_VesselInformation].[LRN]")) Then
    MsgBox "There is no Casualty Information on file for this ship"
    Exit Sub
    End If

    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria

    Exit_ButtonCasualtyHistor_Click:
    Exit Sub

    Err_ButtonCasualtyHistor_Click:
    MsgBox Error$
    Resume Exit_ButtonCasualtyHistor_Click

    End Sub

    Private Sub ButtonOpenChooseShip_Click()
    On Error GoTo Err_ButtonOpenChooseShip_Click

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_ChooseShipFromShipsInformationTable"
    LinkCriteria = "[LRND] = Forms![frm_VesselInformation]![LRND]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria
    Forms![frm_ChooseShipFromShipsInformationTable]![cbSelectShipName] = ""
    Forms![frm_ChooseShipFromShipsInformationTable]![cbSelectShipName].SetFocus

    Exit_ButtonOpenChooseShip_Click:
    Exit Sub

    Err_ButtonOpenChooseShip_Click:
    MsgBox Error$
    Resume Exit_ButtonOpenChooseShip_Click

    End Sub

    Private Sub ButtonOperatorInfo_Click()
    On Error GoTo ButtonOperatorInfo_Click_Err:

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_VesselOperatorDataInput"
    LinkCriteria = "[VesselOperator] = Forms![frm_VesselInformation]![VesselOperator]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria

    Exit Sub

    ButtonOperatorInfo_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonOwnerInfo_Click()
    On Error GoTo ButtonOwnerInfo_Click_Err:

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_VesselOwnerDataInput"
    LinkCriteria = "[Owner] = Forms![frm_VesselInformation]![Owner]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria

    Exit Sub

    ButtonOwnerInfo_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonPrintRecord_Click()
    On Error GoTo ButtonPrintRecord_Click_Err:

    DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_SELECTRECORD_V2, , A_MENU_VER20
    DoCmd.PrintOut A_SELECTION

    Exit Sub

    ButtonPrintRecord_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonReports_Click()

    DoCmd.OpenForm "Frm_reports"

    End Sub

    Private Sub ButtonViewStatus_Click()
    On Error GoTo ButtonViewStatus_Click_Err:

    If IsNull(Me![LRN]) Then
    MsgBox "Please choose a ship"
    Exit Sub
    End If

    If IsNull(DLookup("[lrn]", "tbl_JobHistory", "[lrn]=Forms![frm_VesselInformation].[lrn]")) Then
    MsgBox "There are no records for this ship"
    Exit Sub
    Else
    Dim DoCurrentShipName As String
    Dim LinkCriteria As String
    DoCurrentShipName = "frm_JobHistory_Status"
    LinkCriteria = "[LRN] = Forms![frm_VesselInformation]![LRN]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria
    End If

    Exit Sub

    ButtonViewStatus_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub cbShipName_AfterUpdate()
    On Error GoTo cbShipName_AfterUpdate_Err:

    DoCmd.ApplyFilter "SELECT DISTINCTROW tbl_VesselInformation.LRN, tbl_VesselInformation.LRNNumeric,tbl_VesselInforma tion.VesselName, tbl_VesselInformation.Registry, tbl_VesselInformation.ClassSociety, tbl_VesselInformation.VesselType, tbl_VesselInformation.YearBuilt, tbl_VesselInformation.DeadWeight, tbl_VesselInformation.LOA, tbl_VesselInformation.Beam, tbl_VesselInformation.Draft, tbl_VesselInformation.InertGas, tbl_VesselInformation.NitrogenGenerator, tbl_VesselInformation.NitrogenManifold, tbl_VesselInformation.NitrogenBottles,tbl_VesselIn formation.Customer, tbl_VesselInformation.Owner, tbl_VesselInformation.ShipPhoneNumber, tbl_VesselInformation.ShipFaxNumber, tbl_VesselInformation.VesselOperator, tbl_VesselInformation.BowToManifoldDistance FROM tbl_VesselInformation;", "Forms![frm_VesselInformation].[cbShipName] = [tbl_VesselInformation].[LRN]"

    Exit Sub

    cbShipName_AfterUpdate_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub cbShipName_GotFocus()
    On Error GoTo cbShipName_GotFocus_Err:

    [cbShipName].Requery

    Exit Sub

    cbShipName_GotFocus_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub Form_AfterInsert()
    On Error GoTo Form_AfterInsert_Err:

    'Forms![frm_VesselInformation]![cbShipName].Requery

    Exit Sub

    Form_AfterInsert_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub Form_Current()
    On Error GoTo Form_Current_Err:

    'Forms![frm_VesselInformation]![cbShipName].Requery

    Exit Sub

    Form_Current_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Form_Open_Err:

    Me![cbShipName].SetFocus
    'previously called ShipName macro

    'DoCmd ShowToolbar "", a_toolbar_yes

    Exit Sub

    Form_Open_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub
    Last edited by jbw92; 01-08-04 at 15:27.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Refresh

    You have to tell us which button should refresh the data. Looks like you have tons of buttons on your screen.


    Originally posted by jbw92
    Hi All,

    I'm having a problem after conversion of an Access 2.0 database into Access 2K
    After a selection is made by means of a combobox the underlying data is not refreshed.
    When i use the following command's, Remove Filter / Filter by Form / Apply Filter
    after a selection the data is refreshed.

    I assume the problem is the vba code. As i'm not very familiar with vba code i'm asking for your assistance.

    btw
    The data is refreshed when i open the database in Access 97. However that's not the intention.

    Attached is the form's vba code.

    TIA
    Joop

    As i do not see the attachment code as follows
    Option Compare Database 'Use database order for string comparisons


    Private Sub ButtonAddOperator_Click()
    On Error GoTo ButtonAddOperator_Click_Err:

    DoCmd.OpenForm "frm_Operator", , , A_ADD

    Exit Sub

    ButtonAddOperator_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonAddOwner1_Click()
    On Error GoTo Err_ButtonAddOwner1_Click

    DoCmd.OpenForm "frm_owner", , , A_ADD

    Exit_ButtonAddOwner1_Click:
    Exit Sub

    Err_ButtonAddOwner1_Click:
    MsgBox Error$
    Resume Exit_ButtonAddOwner1_Click

    End Sub

    Private Sub ButtonCasualtyHistor_Click()
    On Error GoTo Err_ButtonCasualtyHistor_Click

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_CasualtyInformation"
    LinkCriteria = "[LRND] = Forms![frm_VesselInformation]![LRN] "

    If IsNull(DLookup("[LRND]", "tbl_CAS_CasualtyData", "[lrnd]=[Forms]![frm_VesselInformation].[LRN]")) Then
    MsgBox "There is no Casualty Information on file for this ship"
    Exit Sub
    End If

    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria

    Exit_ButtonCasualtyHistor_Click:
    Exit Sub

    Err_ButtonCasualtyHistor_Click:
    MsgBox Error$
    Resume Exit_ButtonCasualtyHistor_Click

    End Sub

    Private Sub ButtonOpenChooseShip_Click()
    On Error GoTo Err_ButtonOpenChooseShip_Click

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_ChooseShipFromShipsInformationTable"
    LinkCriteria = "[LRND] = Forms![frm_VesselInformation]![LRND]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria
    Forms![frm_ChooseShipFromShipsInformationTable]![cbSelectShipName] = ""
    Forms![frm_ChooseShipFromShipsInformationTable]![cbSelectShipName].SetFocus

    Exit_ButtonOpenChooseShip_Click:
    Exit Sub

    Err_ButtonOpenChooseShip_Click:
    MsgBox Error$
    Resume Exit_ButtonOpenChooseShip_Click

    End Sub

    Private Sub ButtonOperatorInfo_Click()
    On Error GoTo ButtonOperatorInfo_Click_Err:

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_VesselOperatorDataInput"
    LinkCriteria = "[VesselOperator] = Forms![frm_VesselInformation]![VesselOperator]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria

    Exit Sub

    ButtonOperatorInfo_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonOwnerInfo_Click()
    On Error GoTo ButtonOwnerInfo_Click_Err:

    Dim DoCurrentShipName As String
    Dim LinkCriteria As String

    DoCurrentShipName = "frm_VesselOwnerDataInput"
    LinkCriteria = "[Owner] = Forms![frm_VesselInformation]![Owner]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria

    Exit Sub

    ButtonOwnerInfo_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonPrintRecord_Click()
    On Error GoTo ButtonPrintRecord_Click_Err:

    DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, A_SELECTRECORD_V2, , A_MENU_VER20
    DoCmd.PrintOut A_SELECTION

    Exit Sub

    ButtonPrintRecord_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub ButtonReports_Click()

    DoCmd.OpenForm "Frm_reports"

    End Sub

    Private Sub ButtonViewStatus_Click()
    On Error GoTo ButtonViewStatus_Click_Err:

    If IsNull(Me![LRN]) Then
    MsgBox "Please choose a ship"
    Exit Sub
    End If

    If IsNull(DLookup("[lrn]", "tbl_JobHistory", "[lrn]=Forms![frm_VesselInformation].[lrn]")) Then
    MsgBox "There are no records for this ship"
    Exit Sub
    Else
    Dim DoCurrentShipName As String
    Dim LinkCriteria As String
    DoCurrentShipName = "frm_JobHistory_Status"
    LinkCriteria = "[LRN] = Forms![frm_VesselInformation]![LRN]"
    DoCmd.OpenForm DoCurrentShipName, , , LinkCriteria
    End If

    Exit Sub

    ButtonViewStatus_Click_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub cbShipName_AfterUpdate()
    On Error GoTo cbShipName_AfterUpdate_Err:

    DoCmd.ApplyFilter "SELECT DISTINCTROW tbl_VesselInformation.LRN, tbl_VesselInformation.LRNNumeric,tbl_VesselInforma tion.VesselName, tbl_VesselInformation.Registry, tbl_VesselInformation.ClassSociety, tbl_VesselInformation.VesselType, tbl_VesselInformation.YearBuilt, tbl_VesselInformation.DeadWeight, tbl_VesselInformation.LOA, tbl_VesselInformation.Beam, tbl_VesselInformation.Draft, tbl_VesselInformation.InertGas, tbl_VesselInformation.NitrogenGenerator, tbl_VesselInformation.NitrogenManifold, tbl_VesselInformation.NitrogenBottles,tbl_VesselIn formation.Customer, tbl_VesselInformation.Owner, tbl_VesselInformation.ShipPhoneNumber, tbl_VesselInformation.ShipFaxNumber, tbl_VesselInformation.VesselOperator, tbl_VesselInformation.BowToManifoldDistance FROM tbl_VesselInformation;", "Forms![frm_VesselInformation].[cbShipName] = [tbl_VesselInformation].[LRN]"

    Exit Sub

    cbShipName_AfterUpdate_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub cbShipName_GotFocus()
    On Error GoTo cbShipName_GotFocus_Err:

    [cbShipName].Requery

    Exit Sub

    cbShipName_GotFocus_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub Form_AfterInsert()
    On Error GoTo Form_AfterInsert_Err:

    'Forms![frm_VesselInformation]![cbShipName].Requery

    Exit Sub

    Form_AfterInsert_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub Form_Current()
    On Error GoTo Form_Current_Err:

    'Forms![frm_VesselInformation]![cbShipName].Requery

    Exit Sub

    Form_Current_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Form_Open_Err:

    Me![cbShipName].SetFocus
    'previously called ShipName macro

    'DoCmd ShowToolbar "", a_toolbar_yes

    Exit Sub

    Form_Open_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    Thanks for the reply,

    i think the following code is the reason
    Private Sub cbShipName_AfterUpdate()
    On Error GoTo cbShipName_AfterUpdate_Err:

    DoCmd.ApplyFilter "SELECT DISTINCTROW tbl_VesselInformation.LRN, tbl_VesselInformation.LRNNumeric,tbl_VesselInforma tion.VesselName, tbl_VesselInformation.Registry, tbl_VesselInformation.ClassSociety, tbl_VesselInformation.VesselType, tbl_VesselInformation.YearBuilt, tbl_VesselInformation.DeadWeight, tbl_VesselInformation.LOA, tbl_VesselInformation.Beam, tbl_VesselInformation.Draft, tbl_VesselInformation.InertGas, tbl_VesselInformation.NitrogenGenerator, tbl_VesselInformation.NitrogenManifold, tbl_VesselInformation.NitrogenBottles,tbl_VesselIn formation.Customer, tbl_VesselInformation.Owner, tbl_VesselInformation.ShipPhoneNumber, tbl_VesselInformation.ShipFaxNumber, tbl_VesselInformation.VesselOperator, tbl_VesselInformation.BowToManifoldDistance FROM tbl_VesselInformation;", "Forms![frm_VesselInformation].[cbShipName] = [tbl_VesselInformation].[LRN]"

    Exit Sub

    cbShipName_AfterUpdate_Err:
    MsgBox "The following error occured: " & Error$
    Resume Next
    End Sub

    however as i stated my vba knowledge/experience is limited.

    When the database is opened the user can make a selection using a combobox. After the selection is made the data displayed on screen needs to be refreshed.
    The database consists of 2 mdb files a ...sys and a ...dat file.
    The data is stored in the ...dat file and the form is stored in the ...sys file. Using the linked table manager the databases are linked.
    The ...dat file is updated and distributed weekly.

    Is this helpful?

    TIA
    Joop

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Okay if I understand correctly, You have a form bound by a query and all the controls on the form are also bound to the fields of the form's record source, right ?? If thats the case and you want to select the record depending upon the selection in your combo box then this is what you need to do..

    on the After_Update event of the combo box use these lines of code.

    Me.RecordsetClone.FindFirst "[RECORD_ID]=" & Me.cboPickRecord
    Me.Bookmark = Me.RecordsetClone.Bookmark

    where cboPickRecord is your combo box used to pick record.

    Let me know if that helps otherwise send me your form and I'll take a look.

  5. #5
    Join Date
    Sep 2003
    Posts
    3
    Hi Khan,

    Thanks for the reply.
    I found a solution already however i'm not really happy with it so I'll try your suggestion as soon as possible and will let you know.

    Joop

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •