Push results from a search box back to a table 1

[colinmitton]

I have a database with my 3rd party businesses, the contacts associated with them and all the employees where I work.

I'm now setting up a small projects 'job sheet' so we can select a business, a contact from that business and an employee who's working with them. I've setup a new table, linking the unique ID's of the 3 different parties (business / contact / employee).

My whole idea is to set up a form which has a button to bring a small pop up window with a list of businesses I select on from the list click a button and the unique ID get copied in to the project table keeping the 'link' from the business table. Which will mean that I can then use the unique ID number for my mailmerges.

My question is once I've selected what I require from the pop up window how do I get the result back to project table? I've used the 'wherecondition' to move on to a different form using a selected criteria but no populated a table.

Any idea's are welcome.

 

[MazeWorX]

An Append query will add new data to a table. An update query edits data. VBA can also achieve the same results with Recordsets and .AddNew, .Edit . You could run a query with the AfterUpdate event of the control on the popup form or the ONClose event or even add a button and the OnClick event. I prefer adding a button myself

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[colinmitton]

Thanks MazeWorx, as I'm quite new to access so I'm in the process of creating the form to add in the the various parties to the project form. Would you advise a particular way of doing it (i.e. is there a 'stardard' way)? I thought having a pop up and select from a list (then press an 'ok' button) would be most obvious but is that the best way?

You should also note that I'm planning to move the Access DB to my MS-SQL server so the users can use access as an interface and the DB sit happily in the background!

I like the sound of using the recordsets method and will have a look in to it. do you have any sample code to help me along

 

[colinmitton]

I've been looking in to recordsets, how do I reference the the open record on the 'project' form (which has the button to open the business list) so that when I select the business and .edit to add in the unique ID? will the

db.openrecordset("project_table")

Work?
So I can then use the .edit command for the rel;event field using the 'string' I slected from the list?

I hope that makes sense!

 

[MazeWorX]

it depends do you want to use a list to populate the project field then save your project? or save it as you populate it the later would be the preferred method if you want to use a popup form or you could use a combo control to populate the field (even easier). Thoughts?



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[colinmitton]

The pop up window will be better than a combo box as my users are used to that sort of thing and when I improve fill aadd filters to the pop up window to narrow down the selection criteria.

I've been working on it and so far have go this:

Private Sub cmd_use_emp_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("tbl_3rd_P_Projects")
Dim EmpID As String
Dim EMPFName As String
Dim EMPLName As String
Dim ProjID As String
    EmpID = [frm_Qlookup_Employees_short].[Form]![EmployeeID]
    EMPFName = [frm_Qlookup_Employees_short].[Form]![FirstName]
    EMPLName = [frm_Qlookup_Employees_short].[Form]![LastName]
    ProjID = [Forms]![Frm_3rd_P_Projects_Create]![ProjectID]
Forms.Frm_3rd_P_Projects_Create.SetFocus
With rst
    .FindFirst "ProjectID = " & ProjID
    If Not .NoMatch Then
    .Edit
    !EmployeeID = EmpID
    !EmpFName = EMPFName
    !EmpLName = EMPLName
    .Update
  End If
End With
End Sub

This is from my employee list pop up form. I'm having trouble once selecting the employee I want on the project writing it back to the active record in the projects table.
This part:

ProjID = [Forms]![Frm_3rd_P_Projects_Create]![ProjectID]
Forms.Frm_3rd_P_Projects_Create.SetFocus
With rst
    .FindFirst "ProjectID = " & ProjID

Is picking up the correct Project ID but when I do the .findfirst part its not pulling through the ID?

 

[MazeWorX]

I would do it like this since the popup has focus then just reference the control also setting focus shouldnt be required assuming the popup is Frm_3rd_P_Projects_Create

Private Sub cmd_use_emp_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim EmpID As String
    Dim EMPFName As String
    Dim EMPLName As String
    Dim ProjID As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tbl_3rd_P_Projects")

    EmpID = [frm_Qlookup_Employees_short].[Form]![EmployeeID]
    EMPFName = [frm_Qlookup_Employees_short].[Form]![FirstName]
    EMPLName = [frm_Qlookup_Employees_short].[Form]![LastName]
    'ProjID = [Forms]![Frm_3rd_P_Projects_Create]![ProjectID]

    Forms.Frm_3rd_P_Projects_Create.SetFocus

    With rst
        .FindFirst "ProjectID = " & Me.ProjectID
        If Not .NoMatch Then
            .Edit
            .Fields("EmployeeID") = EmpID
            .Fields("EMPFName") = EMPFName
            .Fields("EMPLName") = EMPLName
            .Update
        End If
    End With

    Set rst = Nothing
    Set db = Nothing
End Sub

You can add a debug.print Me.ProjectID and it will print the value of the control in the intermediate window of the editor

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[colinmitton]

Not quite, The pop up has a sub form within it and 'Frm_3rd_P_Projects_Create' is the orignal form where all the project details are held.

In the end I did a search on my error code (why i did not think of that first!) found I need to declare the EmpID as a long interger then open the recordest with 'OpenDynaset' in it so adjusted my code accordingly:

Private Sub cmd_use_emp_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("tbl_3rd_P_Projects", dbOpenDynaset)
Dim EmpID As String
Dim EMPFName As String
Dim EMPLName As String
Dim ProjID As Long

    EmpID = [frm_Qlookup_Employees_short].[Form]![EmployeeID]
    EMPFName = [frm_Qlookup_Employees_short].[Form]![FirstName]
    EMPLName = [frm_Qlookup_Employees_short].[Form]![LastName]
    ProjID = [Forms]![Frm_3rd_P_Projects_Create]![ProjectID]
Forms.Frm_3rd_P_Projects_Create!ProjectID.SetFocus
With rst
    .FindFirst "ProjectID = " & ProjID
    If Not .NoMatch Then
    .Edit
    !EmployeeID = EmpID
    !EmpFName = EMPFName
    !EmpLName = EMPLName
    .Update
  End If
End With

Forms.Frm_Employee_PopupList.SetFocus
    DoCmd.Close acForm, Me.Name
Forms.Frm_3rd_P_Projects_Create.Requery

Set db = Nothing
Set rst = Nothing

End Sub

Thanks for your help.