query then modify table from form

[Chris L]

I have a table that has the following fields
ID, Num, Type, ReportTo
ReportTo has a value of certain ID.

Then I have a form named fm_List with a combo box named cmbType & cmbReportTo.
with cmbReportTo displaying the Num field generated with the following query:

SELECT tbl.ID, tbl.Num FROM tbl WHERE (((tbl.ReportTo) Is Null) AND
((tbl.Type)=[Forms]![fm_List].[form]![cmbType])) ORDER BY tbl.Num;

The user can input certain Num into the cmbReportTo combo box. if the Num is
not in the list, then I want the NotInList event handler to search the
corresponding ID of the Num entered by the user and set the ReportTo to that
ID number.

Can somebody guide me on how can I implement this?

 

[Ken Snell [MVP]]

Are you currently using the NotInList event to allow the user to enter a new
Num value? If yes, post that code so that we can see what you're doing --
normally, this code would do what you seek, so something may be missing from
it.

If you're not using the NotInList event now to handle this, are you just
letting a user enter a new Num into the combo box?

 

[Chris L]

Hi,

Thank you for the reply.

I attempted to use the NotInList event handler, but it didn`t work,
so I move the processing to AfterUpdate.

Private Sub ReportTo_AfterUpdate()
'Definitions
Dim db As Database
Dim rs As Recordset
Dim strSql As String

Set db = CurrentDb

' // Build SQL statement
' // Find ID when user enter Num into ReportTo combobox
strSql = ""
strSql = strSql & "SELECT ID, Type, Num "
strSql = strSql & "FROM tbl "
strSql = strSql & "WHERE tbl.Num = " & Me.ReportTo
strSql = strSql & " And tbl.Type = " & Me.Type

Set rs = db.OpenRecordset(strSql)

' // If ID found
If rs.EOF = False Then
rs.MoveFirst

' // Set the current ReportTo with ID
strSql = ""
strSql = strSql & "UPDATE tbl "
strSql = strSql & "SET ReportTo = " & rs.Fields("ID") & " "
strSql = strSql & "WHERE Num = " & Me.Num
strSql = strSql & " And Type = " & Me.Type

DoCmd.RunSQL (strSql)

End If

rs.Close
Set rs = Nothing
Set db = Nothing

' // Requery ReportTo to display the modified value
Me.ReportTo.Requery
End Sub

The problem is the sql command modifies the current ReportTo from internally
and conflicting the current record.
Furthermore, Me.ReportTo.Requery does not update the combobox list even
though the table has changed.

Can you guide me if there is a different approach to solve the problem.

Thanks & Regards,

 

[Ken Snell [MVP]]

When you attempted to use the NotInList event, did you also set the Limit To
List property to Yes?

Post the code that you tried to use for NotInList event procedure. It's
really the easiest way to do what you want to do.

 

[Chris L]

Hi Thanks for the reply again.
Solved the problem using a different approach.

I created a dummy unbound edit box which is displayed on the form
to contain the user input and update `ReportTo` value on After Update event.

Regards,
Chris