User Defined Types

[Guest]

On a form I have a listbox-list of Providers.
When a command button is clicked to add another Provider another form opens
to enter a new Provider number. The code below is to save that information
and update the list on the main form.

I cannot get the list of Providers to be specific to that one record.
Another words all providers show up on the list. I think I need to create a
DataType which I'm trying to do here "Dim db As DAO.Database". How do I
define this???


Private Sub cmdSave_Click()
On Error GoTo cmdAddProviderErr
Dim strprovno As String
Dim cmplnt As Boolean
Dim db As DAO.Database
Dim q As String

If IsNull(Me.txtProvno) Or Me.txtProvno = "" Then
MsgBox "Please enter a provider number"
Exit Sub
End If
strprovno = Me.txtProvno
cmplnt = Me.chkComplaint

q = "INSERT INTO TBLQUALITYPROVIDER (ICNNO, PROVNO, CREATETIME,
COMPLAINT) VALUES ('" & OpenArgs _
& "','" & strprovno & "', #" & Now() & "#, " & Me.chkComplaint & " );"
Set db = CurrentDb
db.Execute q, dbFailOnError
db.close
DoEvents
Form_frmQualityData.refreshProviderLst
DoCmd.close
Exit Sub
cmdAddProviderErr:
MsgBox Err.Number & " " & Err.Description
Exit Sub

End Sub

 

[Guest]

Base your listbox on a query.
Use the appropriate field on your form as the criteria of your query.
Requery the listbox in the form's OnCurrent event.

Steve