Access ComboBox binding and Selected Value

Question

I am trying to create a simple form for entering data. I have two tables, jobs and reports. The report table refers to a job with a one to many (one job, many reports). When browsing through the reports I want the combo box that lists all of the jobs to show the corresponding job as the selected value. This is easy in a .NET environment, but I'm not understanding how to set this up in the property sheet for the combobox. My ComboBox record source is from a query:

SELECT Jobs.UID, Jobs.Projectcode, Jobs.Projectname, Jobs.Owner, Jobs.Contractor
FROM Jobs
ORDER BY Jobs.[Projectcode];

And the form is based on a query that joins the tables:

SELECT Report.ID, Report.ReportNumber, Report.ReportDate, Report.Temperature, Report.Weather, Report.Progress, Report.PeopleatOAC, Report.Trades, Jobs.UID, Jobs.Projectname, Jobs.Owner, Jobs.Contractor
FROM Report
INNER JOIN Jobs ON Jobs.UID = Report.JobID
UNION ALL SELECT  Report.ID, Report.ReportNumber, Report.ReportDate, 
Report.Temperature, Report.Weather, Report.Progress, Report.PeopleatOAC, 
Report.Trades, Jobs.UID, Jobs.Projectname, Jobs.Owner, Jobs.Contractor
FROM Report
LEFT JOIN Jobs ON Jobs.UID = Report.JobID WHERE (((Report.JobID) Is Null))
ORDER BY Report.ID;

The way I have this set up, a report can have a null job field. So I want to be able to select a job from the combo box to update the report table AND I want the combo box to reflect the correct job if the current record has a jobID associated with it. Is this possible?

Paul Gibson Paul Gibson · Accepted Answer · 2018-07-25T23:30:36

What I have implemented is a tie to the Current event for the form that sets the combobox value (or clears it) depending on the JobID. As well as an tied to the changed event for the combobox to update the database with a selection. This works well enough but VBA feels so limiting compared to C#, WPF, and MVVM.

For anyone stumbling accross this with a similar question here are the 2 VBA functions:

Private Sub Form_Current()
Dim JobID As Integer
Dim i As Integer
Dim TempVal As Variant
Dim TestVal As Integer

TempVal = Me.JobID.Value
If Me.JobID.Value <> Empty Then
JobID = Me.JobID.Value
Else: JobID = -2
End If



With Me.JobCodeCombo
    If (JobID >= 0) Then
       For i = 0 To .ListCount - 1
          TestVal = .Column(0, i)
          If .Column(0, i) = JobID Then
             .Value = .ItemData(i)
             Exit For
          End If
       Next
    Else
        Me.JobCodeCombo = Null
    End If
End With

End Sub

Private Sub JobCodeCombo_Change()
    Dim ReportID As Long
    Dim JobID As Long
    Dim dbs As DAO.Database
    Dim qdfUpdateJobID As DAO.QueryDef
    Dim CurrentRecord As Long

    CurrentRecord = Me.CurrentRecord
    Set dbs = CurrentDb
    Set qdfUpdateJobID = dbs.QueryDefs("UpdateReportWithJobID")

    ReportID = Me.ReportID.Value
    JobID = Me.JobCodeCombo.Column(0)

    qdfUpdateJobID.Parameters(0).Value = JobID
    qdfUpdateJobID.Parameters(1).Value = ReportID
    qdfUpdateJobID.Execute
    qdfUpdateJobID.Close
    DoCmd.Save acForm, "Form1"
    Me.Requery
    DoCmd.GoToRecord acDataForm, "Form1", acGoTo, CurrentRecord
End Sub

The query called from the second function is a simple update query in my access file that has two parameters:

PARAMETERS [P1] Long, [P2] Long;
UPDATE Report SET JobID = P1
WHERE [ID] = P2;

Access ComboBox binding and Selected Value

1 Answers