Updating a list box

[battenberg]

Hi

I have a list box which contains project references for our customers,
this list box is populated from a query. I want to be able to update the project ref and at the same time update the list box.

how do I update the list box so that the query references a particular value in a field on a form and applies it as a condition in the update.?

something along the lines of:

on exit of the 'project ref' field, capture what is in the customer field "strCustomerFieldData", apply it to the query under 'customer' (Like "strCustomerFieldData") then refresh the list box.

thanks a million

Battenberg

 

[Steve R.]

Below is some test code that I have been working on to add a new author to a combobox that uses a query to generate a list of names and adds that name if it is not in the drop down list. Please be aware that this code has not been verified, I am still testing. PS: If anyone sees room for improvement of this code, please post.

***Update: Code below not working, looks like I will need to make my own post on this.

Part 1 This code triggered by the NotInList event.

Private Sub Combo0_NotInList(newdata As String, response As Integer)
Rem Created 2/12/2006
Rem revised 9/23/2006
Debug.Print "New data ="; newdata
If newdata = "" Then Me![Combo0] = 2
If newdata = "" Then Exit Sub

Call AddAuthor.NewAuthor(newdata, response)
If newdata = "" Then Me![Combo0] = Null

End Sub

part 2 This code executed by the code above. Placed in the MODULES section so that other listboxes/comboboxes can run it.

Public Sub NewAuthor(newdata As String, response As Integer)
Rem Created 2/12/2006
Rem revised 9/4/2006
Dim oktoproceed As Boolean
Dim MSG1 As String
Dim MSG2 As String
Dim TITLE As String
Debug.Print "New data ="; newdata

oktoproceed = False
Rem Exit this subroutine if the combobox was cleared.
Rem If oktoproceed = False Then NewData = ""
Rem ----------------------------------------------------
Rem "Ask the user if a new author is to be added"
MSG1 = "'" & newdata & "' is not in the author list." & CR & CR & "Do You Want to add this author?"
MSG2 = "Operation Cancelled"
TITLE = "Add New Author"

Rem exit program if you don't want to add new author
If MsgBox(MSG1, vbQuestion + vbYesNo) = vbNo Then MsgBox MSG2, , TITLE: newdata = "": response = acDataErrContinue: Exit Sub
rem =====================================================

****PARSING CODE SKIPPED*****

Rem =============================================================================================
Rem End of Name parsing
Rem add the new author to the author table
Rem =============================================================================================
Dim dbstemp As DAO.Database
Dim rsttemp As DAO.Recordset
Dim authortemp As String
Set dbstemp = CurrentDb
authortemp = "select * from authortemp"
Set rsttemp = dbstemp.OpenRecordset(authortemp, dbOpenDynaset)
rsttemp.AddNew
rsttemp![LAST] = [LASTX]
rsttemp![FIRST] = [FIRSTX]
rsttemp.Update
rsttemp.close
response = acDataErrAdded
Exit Sub

 

[Steve R.]

Update Combo Box Code

I trimmed the code down to the bare minimum and tested it. Now it works as expected to update a combobox.

Part 1 Triggered by NotinList event

Private Sub Combo0_NotInList(newdata As String, response As Integer)

Call addauthor(newdata, response)
Exit Sub

End Sub

Part 2 Subroutine (place in the module section) so that other forms (programs) can access this code.

Private Sub addauthor(newdata As String, response As Integer)
Dim MSG1 As String
Dim MSG2 As String
Dim TITLE As String

Rem Exit this subroutine if the combobox was cleared.
Rem ----------------------------------------------------
Rem "Ask the user if a new author is to be added"
response = acDataErrContinue
MSG1 = "'" & newdata & "' is not in the author list." & CR & CR & "Do You Want to add this author?"
MSG2 = "Operation Cancelled"
TITLE = "Add New Author"

Rem exit program if you don't want to add new author
If MsgBox(MSG1, vbQuestion + vbYesNo) = vbNo Then MsgBox MSG2, , TITLE: newdata = "": response = acDataErrContinue:  Me![Combo0] = 2: Exit Sub
Rem Exit this subroutine if the combobox was cleared.
If newdata = "" Then Exit Sub
Rem ----------------------------------------------------
Dim dbstemp As Database
Dim rsttemp As Recordset
Dim authortemp As String
Set dbstemp = CurrentDb
authortempx = "select * from authortemp"
Set rsttemp = dbstemp.OpenRecordset(authortempx, dbOpenDynaset)
rsttemp.AddNew
rsttemp![LAST] = [newdata]
rsttemp.Update
response = acDataErrAdded
rsttemp.close
Exit Sub
End Sub