Need VBA Help!!!! 
Author Message
 Need VBA Help!!!!

    First off, thanks for any help you can give me! Some would say I know
just enough to be dangerous, I don't think I'm even that good - so please
try to keep it simple. I copied code from the Microsoft Access 2000 Bible
(by Prague and Irwin), here's what I'm trying to do. I have a customers
table and form (unbound text boxes). The form has a find button that calls
up another form with an unbound list box that shows the customer name,
company and customer ID. I have 3 radio buttons on this form that sorts the
list box by name, ID and company. Upon highlighting the customer and
clicking the ok button, the original customer form automatically updates
itself. This all works ok (except for selecting the customer name, the form
does'nt updat-but I can live with that for now), here is my next hurdle: on
another form (workorders form) I want to get the same information to update.
Here is a sample of the code that is working for the customers form.

Private Sub Ok_Click()
On Error GoTo Ok_Click_Err
Dim Criteria As String
Dim MyRS As DAO.Recordset

    Set MyRS = Forms![Customers].RecordsetClone
    Criteria = "[CompanyName] = """ & Me![Search Results] & """"
    MyRS.FindFirst Criteria
    If Not MyRS.NoMatch Then
        Forms![Customers].Bookmark = MyRS.Bookmark
    End If
    MyRS.Close
    Set MyRS = Nothing

    DoCmd.Close acForm, "Customers Sub Form"

Ok_Click_Exit:
    Exit Sub

Ok_Click_Err:
    MsgBox Err.Description
    Resume Ok_Click_Exit

End Sub

Private Sub Search_By_AfterUpdate()
    With CodeContextObject
    If [Search By] = 1 Then
    [Search Results].RowSource = ""
    [Search Results].ColumnCount = 4
    [Search Results].ColumnWidths = "1 in;2 in;2 in;1 in"
    [Search Results].BoundColumn = 1
    [Search Results].RowSource = "SELECT Customers.[CustomerID],
Customers.[CompanyName], Customers.[Primary Contact Name], Customers.[City]
FROM Customers ORDER BY Customers.[CustomerID]"
    ElseIf [Search By] = 2 Then
    [Search Results].RowSource = ""
    [Search Results].ColumnCount = 4
    [Search Results].ColumnWidths = "2 in;1 in;2 in;1 in"
    [Search Results].BoundColumn = 1
    [Search Results].RowSource = "SELECT Customers.[CompanyName],
Customers.[CustomerID], Customers.[Primary Contact Name], Customers.[City]
FROM Customers ORDER BY Customers.[CompanyName]"
    ElseIf [Search By] = 3 Then
    [Search Results].RowSource = ""
    [Search Results].ColumnCount = 4
    [Search Results].ColumnWidths = "2 in;2 in;1 in;1 in"
    [Search Results].BoundColumn = 1
    [Search Results].RowSource = "SELECT Customers.[Primary Contact Name],
Customers.[CompanyName], Customers.[CustomerID], Customers.[City] FROM
Customers ORDER BY Customers.[Primary Contact Name]"
    End If
    End With
End Sub

here is the sql statement from my list box [Search Results]
SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName,
Customers.[Primary Contact Name], Customers.City, Customers.StateOrProvince,
Customers.[Contract Level]
FROM Customers
WHERE ((Not (Customers.CustomerID) Is Null))
ORDER BY Customers.[Primary Contact Name], Customers.CompanyName,
Customers.City;

this works with the exception of option 3.

Here is the code from the Workorders sub form: (in case I'v confused you,
this is what I'm having trouble with)

Private Sub Ok_Click()
On Error GoTo Ok_Click_Err
Dim Criteria As String
Dim MyRS As DAO.Recordset

    Set MyRS = Forms![Workorders].RecordsetClone
    Criteria = "[CompanyName] = """ & Me![Search Results] & """"
    MyRS.FindFirst Criteria
    If Not MyRS.NoMatch Then
        Forms![Workorders].Bookmark = MyRS.Bookmark
    End If
    MyRS.Close
    Set MyRS = Nothing

    DoCmd.Close acForm, "Workorders Sub Form"

Ok_Click_Exit:
    Exit Sub

Ok_Click_Err:
    MsgBox Err.Description
    Resume Ok_Click_Exit

End Sub

Here is the option button code:

Private Sub Search_By_AfterUpdate()
    With CodeContextObject
    If [Search By] = 1 Then
    [Search Results].RowSource = ""
    [Search Results].ColumnCount = 4
    [Search Results].ColumnWidths = "1 in;2 in;2 in"
    [Search Results].BoundColumn = 4
    [Search Results].RowSource = "SELECT Customers.[CustomerID],
Customers.[CompanyName], Customers.[Primary Contact Name] FROM Customers
INNER JOIN Workorders ON [Customers].[CustomerID]=[Workorders].[CustomerID]
ORDER BY Customers.[CustomerID]"
    ElseIf [Search By] = 2 Then
    [Search Results].RowSource = ""
    [Search Results].ColumnCount = 4
    [Search Results].ColumnWidths = "2 in;1 in;2 in"
    [Search Results].BoundColumn = 4
    [Search Results].RowSource = "SELECT Customers.[CompanyName],
Customers.[CustomerID], Customers.[Primary Contact Name] FROM Customers
INNER JOIN Workorders ON [Customers].[CustomerID]=[Workorders].[CustomerID]
ORDER BY Customers.[CompanyName]"
    ElseIf [Search By] = 3 Then
    [Search Results].RowSource = ""
    [Search Results].ColumnCount = 4
    [Search Results].ColumnWidths = "2 in;1 in;2 in"
    [Search Results].BoundColumn = 4
    [Search Results].RowSource = "SELECT Customers.[Primary Contact Name],
Customers.[CustomerID], Customers.[CompanyName] FROM Customers INNER JOIN
Workorders ON [Customers].[CustomerID]=[Workorders].[CustomerID] ORDER BY
Customers.[Primary Contact Name]"
    End If
    End With
End Sub

My problem here is that when I click on the ok button, none of the fields
will update. If you need more info, let me know...



Sat, 18 Sep 2004 05:20:53 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Acc97: Need VBA help on a date range function

2. My Stock Portfolio Needs VBA Help

3. Need VBA Help In Boston.

4. I need VBA help w/ dialog boxes

5. Need help updating a field via VBA

6. VBA - MoveNext / Loop Help needed

7. Need help with VBA - Looping through records?

8. VBA help needed...Urgent

9. vba newbie needs help

10. If/Then VBA expression to use in reports: help needed

11. VBA help needed (to transfer value from activeX calander control to a subform)

12. Help needed with VBA to take data from a recordset to fill a listbox

 

 
Powered by phpBB® Forum Software