G
Guest
I have a database that we use for mailing lists. Two of my forms use a
listbox chooser. One of them is to create a new mailing list, and the other
to edit. When you use the one to edit, all of the names do not show up. After
you add a person in a precious step, they should show up in the list. The
only time they do, is if you add a new list and select them. After this, they
will show up in the edit list. I am very confused with this database, as it
is farely extensive. The edit list runs the query with the next button on the
previous form, and the new list query runs on open form. I will put for you
both sets of code for running the query. Any help is appriciated.
Here is the code for the edit list:
Function EditMLNextButton()
'This takes the name of the mailing list you have created
'and sets up the MultiPik form that is opened
Const FIELD_NULL = 94
On Error GoTo NextError
Dim frm As Form
Dim fld As String
'Store the name of the mailing list
Set frm = Screen.ActiveForm
fld = frm![MailingListName]
'Close current form
DoCmd.Close
'Fill the arrays
Dim dbs As Database
Dim availRst As Recordset
Dim selecRst As Recordset
Dim varAvailable() As Variant 'Init the arrays
Dim varSelected() As Variant 'Init the arrays
Dim availSQL As String
Dim selecSQL As String
Dim availRecordCount As Integer
Dim selecRecordCount As Integer
Dim i As Integer
Dim qry As String
'Initialize the variables
Set dbs = CurrentDb
qry = "qryMultiPikFill"
availSQL = "SELECT [Names] FROM " & qry & " WHERE ( ([" & fld & "] =
NULL) OR ([" & fld & "] = Not -1) );"
selecSQL = "SELECT [Names] FROM " & qry & " WHERE [" & fld & "] = -1;"
Set availRst = dbs.OpenRecordset(availSQL, dbOpenDynaset)
Set selecRst = dbs.OpenRecordset(selecSQL, dbOpenSnapshot)
availRecordCount = availRst.RecordCount
selecRecordCount = selecRst.RecordCount
'Re-initialize the arrays
ReDim varAvailable(availRecordCount) As Variant
ReDim varSelected(selecRecordCount) As Variant
'Fill the varAvailable array
If availRecordCount > 0 Then
availRst.MoveFirst
i = 0
While Not availRst.EOF
varAvailable(i) = availRst![Names]
availRst.MoveNext
i = i + 1
Wend
End If
'Fill the varSelected array
If selecRecordCount > 0 Then
selecRst.MoveFirst
i = 0
While Not selecRst.EOF
varSelected(i) = selecRst![Names]
selecRst.MoveNext
i = i + 1
Wend
End If
'Call the function that does the work
adhFillMultiPikArray frm, varAvailable, varSelected
'open the next form
DoCmd.OpenForm ("frmEditSpecifiedMailingList")
Set frm = Screen.ActiveForm
frm![MLNStorage] = fld
Exit Function
NextError:
If Err.Number = FIELD_NULL Then
MsgBox "Please choose the name of a mailing list."
Else
Call GlobalErrHandler(Err.Number)
End If
End Function
Here is the code for new list:
Private Sub Form_Open(Cancel As Integer)
' Two steps to using Multipik:
' Register the controls (do this only once)
' Fill in the starting data (do this anytime
' you want to restart with new data)
' Pass the names of the list box and action
' controls to basMultiPik.
Call adhRegisterCtlNames(adhcSelectedList, adhcAvailableList, _
adhcAddOneButton, adhcAddAllButton, adhcDeleteOneButton, _
adhcDeleteAllButton)
#If USE_ARRAYS Then
' Fill in the arrays necessary for the two lists
' if you're using arrays.
SetupArrays
adhFillMultiPikArray Me, varAvailable, varSelected
#Else
' Call the generic initialization code for using a field.
Call adhFillMultiPikField(Me, "Names", "qryMultiPikFill")
#End If
End Sub
listbox chooser. One of them is to create a new mailing list, and the other
to edit. When you use the one to edit, all of the names do not show up. After
you add a person in a precious step, they should show up in the list. The
only time they do, is if you add a new list and select them. After this, they
will show up in the edit list. I am very confused with this database, as it
is farely extensive. The edit list runs the query with the next button on the
previous form, and the new list query runs on open form. I will put for you
both sets of code for running the query. Any help is appriciated.
Here is the code for the edit list:
Function EditMLNextButton()
'This takes the name of the mailing list you have created
'and sets up the MultiPik form that is opened
Const FIELD_NULL = 94
On Error GoTo NextError
Dim frm As Form
Dim fld As String
'Store the name of the mailing list
Set frm = Screen.ActiveForm
fld = frm![MailingListName]
'Close current form
DoCmd.Close
'Fill the arrays
Dim dbs As Database
Dim availRst As Recordset
Dim selecRst As Recordset
Dim varAvailable() As Variant 'Init the arrays
Dim varSelected() As Variant 'Init the arrays
Dim availSQL As String
Dim selecSQL As String
Dim availRecordCount As Integer
Dim selecRecordCount As Integer
Dim i As Integer
Dim qry As String
'Initialize the variables
Set dbs = CurrentDb
qry = "qryMultiPikFill"
availSQL = "SELECT [Names] FROM " & qry & " WHERE ( ([" & fld & "] =
NULL) OR ([" & fld & "] = Not -1) );"
selecSQL = "SELECT [Names] FROM " & qry & " WHERE [" & fld & "] = -1;"
Set availRst = dbs.OpenRecordset(availSQL, dbOpenDynaset)
Set selecRst = dbs.OpenRecordset(selecSQL, dbOpenSnapshot)
availRecordCount = availRst.RecordCount
selecRecordCount = selecRst.RecordCount
'Re-initialize the arrays
ReDim varAvailable(availRecordCount) As Variant
ReDim varSelected(selecRecordCount) As Variant
'Fill the varAvailable array
If availRecordCount > 0 Then
availRst.MoveFirst
i = 0
While Not availRst.EOF
varAvailable(i) = availRst![Names]
availRst.MoveNext
i = i + 1
Wend
End If
'Fill the varSelected array
If selecRecordCount > 0 Then
selecRst.MoveFirst
i = 0
While Not selecRst.EOF
varSelected(i) = selecRst![Names]
selecRst.MoveNext
i = i + 1
Wend
End If
'Call the function that does the work
adhFillMultiPikArray frm, varAvailable, varSelected
'open the next form
DoCmd.OpenForm ("frmEditSpecifiedMailingList")
Set frm = Screen.ActiveForm
frm![MLNStorage] = fld
Exit Function
NextError:
If Err.Number = FIELD_NULL Then
MsgBox "Please choose the name of a mailing list."
Else
Call GlobalErrHandler(Err.Number)
End If
End Function
Here is the code for new list:
Private Sub Form_Open(Cancel As Integer)
' Two steps to using Multipik:
' Register the controls (do this only once)
' Fill in the starting data (do this anytime
' you want to restart with new data)
' Pass the names of the list box and action
' controls to basMultiPik.
Call adhRegisterCtlNames(adhcSelectedList, adhcAvailableList, _
adhcAddOneButton, adhcAddAllButton, adhcDeleteOneButton, _
adhcDeleteAllButton)
#If USE_ARRAYS Then
' Fill in the arrays necessary for the two lists
' if you're using arrays.
SetupArrays
adhFillMultiPikArray Me, varAvailable, varSelected
#Else
' Call the generic initialization code for using a field.
Call adhFillMultiPikField(Me, "Names", "qryMultiPikFill")
#End If
End Sub