Multiselect List or Check boxes

I've read through some of the posts here and I just can't quite understand 
how to do this. I have 3 tables: teachers, subjects, & subjects_to_teachers. 
I have relationships established for a many to many relationship btwn 
subjects & teachers. I have the base form which shows teacher info. How do I 
create a control on this form that allows the user to select all subjects 
that apply to a teacher? I would like to use check boxes where the user 
checks all that apply but I'm also interested in knowing how to use a 
multiselect list box to do the same.

Thanks in advance!!!
sparlaman
0
Utf
3/19/2008 3:43:00 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
790 Views

Similar Articles

[PageSpeed] 3

"sparlaman" <sparlaman@discussions.microsoft.com> wrote in message 
news:54499235-B28F-42B5-A62B-8E6A6721E743@microsoft.com...
> I've read through some of the posts here and I just can't quite understand
> how to do this. I have 3 tables: teachers, subjects, & 
> subjects_to_teachers.
> I have relationships established for a many to many relationship btwn
> subjects & teachers. I have the base form which shows teacher info. How do 
> I
> create a control on this form that allows the user to select all subjects
> that apply to a teacher? I would like to use check boxes where the user
> checks all that apply but I'm also interested in knowing how to use a
> multiselect list box to do the same.


I've used checkboxes on a subform, but found it cumbersome and fragile. 
I've used a multiselect list box effectively, like this:

*** Storing Multiple Selections From A List Box ***

The best way to store multiple items in a single field is not to do it
at all.  Instead, use multiple records in a related table to represent
these items.  It's a principle of relational database design that a
single field (column) holds only one datum.

An Access subform is designed to display and edit multiple records from
a related table (these records being related to the record currently
displayed on the main form), and does it with no code at all.  A list
box isn't designed to do this, but for small "pick-lists" I do like the
multiselect list box.  However, you need to use code to read the related
records for each new main record and select the appropriate items in the
list box, and then whenever the list box is updated you need to use code
to update the set of records in the related table.


Here's code from a sample form that represents "family members", with a
list box named "lstHobbies" that represents, for each family member,
that person's hobbies from the list of all possible hobbies.


'----- start of code ----- 
Option Compare Database
Option Explicit

Private Sub ClearHobbySelections()

    Dim intI As Integer

    With Me.lstHobbies
        For intI = (.ItemsSelected.Count - 1) To 0 Step -1
            .Selected(.ItemsSelected(intI)) = False
        Next intI
    End With

End Sub

Private Sub Form_Current()

    Dim rs As DAO.Recordset
    Dim intI As Integer

    ' Clear all currently selected hobbies.
    ClearHobbySelections

    If Not Me.NewRecord Then

        Set rs = CurrentDb.OpenRecordset( _
            "SELECT HobbyID FROM tblFamilyMembersHobbies " & _
                "WHERE MemberID=" & Me.MemberID)

        ' Select the hobbies currently on record for this MemberID.
        With Me.lstHobbies
            Do Until rs.EOF
                For intI = 0 To (.ListCount - 1)
                    If .ItemData(intI) = CStr(rs!HobbyID) Then
                        .Selected(intI) = True
                        Exit For
                    End If
                Next intI
                rs.MoveNext
            Loop
            rs.Close
            Set rs = Nothing
        End With

    End If

End Sub

Private Sub lstHobbies_AfterUpdate()

    On Error GoTo Err_lstHobbies_AfterUpdate

    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim strSQL As String
    Dim blnInTransaction As Boolean
    Dim varItem As Variant

    ' Make sure the current member record has been saved.
    If Me.Dirty Then Me.Dirty = False

    Set ws = Workspaces(0)
    Set db = ws.Databases(0)

    ws.BeginTrans
    blnInTransaction = True

    ' Delete all hobbies now on record.
    strSQL = "DELETE FROM tblFamilyMembersHobbies " & _
                "WHERE Memberid = " & Me.MemberID

    db.Execute strSQL, dbFailOnError

    ' Add each hobby selected in the list box.
    With Me.lstHobbies
        For Each varItem In .ItemsSelected
            strSQL = _
                "INSERT INTO tblFamilyMembersHobbies " & _
                    "(MemberID, HobbyID) VALUES (" & _
                    Me.MemberID & ", " & .ItemData(varItem) & ")"
            db.Execute strSQL, dbFailOnError
        Next varItem
    End With

    ws.CommitTrans
    blnInTransaction = False

Exit_lstHobbies_AfterUpdate:
    Set db = Nothing
    Set ws = Nothing
    Exit Sub

Err_lstHobbies_AfterUpdate:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbExclamation, "Unable to Update"
    If blnInTransaction Then
        ws.Rollback
        blnInTransaction = False
    End If
    Resume Exit_lstHobbies_AfterUpdate

End Sub

Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)

    Dim intI As Integer

    ' Don't allow hobbies to be updated before a MemberID has
    ' been generated.
    If IsNull(Me.MemberID) Then
        MsgBox "Please enter other information for this family " & _
            "member before choosing hobbies.", , _
            "Define Member First"
        Cancel = True
        Me.lstHobbies.Undo
        ' Clear the user's selection.
        ClearHobbySelections
    End If

End Sub
'----- end of code ----- 

As you see, there's a fair amount of code involved, because we're using
the list box to do something it wasn't built to do, but it works quite
nicely.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
3/19/2008 3:52:52 PM
I can see by your answer that my question was not clear. I do understand the 
principles of a relational database and I am not trying to store multiple 
items in a single field.

I am trying to use form controls i.e. checkboxes and multiselect list boxes 
to populate  multiple records from a related table. So let me try to explain 
again... I have a table "teachers_to_subjects" where each record contains one 
teacher & one subject. It is in this manner that I am relating teachers to 
subject in a many-to-many fashion. What I want is a form control that will 
allow a user to select subjects per teacher thereby populating my 
"teachers_to_subjects" table.

If you understand what I mean and can help me accomplish this. I'll greatly 
appreciate it.

Thanks,
Sparlaman


"Dirk Goldgar" wrote:

> "sparlaman" <sparlaman@discussions.microsoft.com> wrote in message 
> news:54499235-B28F-42B5-A62B-8E6A6721E743@microsoft.com...
> > I've read through some of the posts here and I just can't quite understand
> > how to do this. I have 3 tables: teachers, subjects, & 
> > subjects_to_teachers.
> > I have relationships established for a many to many relationship btwn
> > subjects & teachers. I have the base form which shows teacher info. How do 
> > I
> > create a control on this form that allows the user to select all subjects
> > that apply to a teacher? I would like to use check boxes where the user
> > checks all that apply but I'm also interested in knowing how to use a
> > multiselect list box to do the same.
> 
> 
> I've used checkboxes on a subform, but found it cumbersome and fragile. 
> I've used a multiselect list box effectively, like this:
> 
> *** Storing Multiple Selections From A List Box ***
> 
> The best way to store multiple items in a single field is not to do it
> at all.  Instead, use multiple records in a related table to represent
> these items.  It's a principle of relational database design that a
> single field (column) holds only one datum.
> 
> An Access subform is designed to display and edit multiple records from
> a related table (these records being related to the record currently
> displayed on the main form), and does it with no code at all.  A list
> box isn't designed to do this, but for small "pick-lists" I do like the
> multiselect list box.  However, you need to use code to read the related
> records for each new main record and select the appropriate items in the
> list box, and then whenever the list box is updated you need to use code
> to update the set of records in the related table.
> 
> 
> Here's code from a sample form that represents "family members", with a
> list box named "lstHobbies" that represents, for each family member,
> that person's hobbies from the list of all possible hobbies.
> 
> 
> '----- start of code ----- 
> Option Compare Database
> Option Explicit
> 
> Private Sub ClearHobbySelections()
> 
>     Dim intI As Integer
> 
>     With Me.lstHobbies
>         For intI = (.ItemsSelected.Count - 1) To 0 Step -1
>             .Selected(.ItemsSelected(intI)) = False
>         Next intI
>     End With
> 
> End Sub
> 
> Private Sub Form_Current()
> 
>     Dim rs As DAO.Recordset
>     Dim intI As Integer
> 
>     ' Clear all currently selected hobbies.
>     ClearHobbySelections
> 
>     If Not Me.NewRecord Then
> 
>         Set rs = CurrentDb.OpenRecordset( _
>             "SELECT HobbyID FROM tblFamilyMembersHobbies " & _
>                 "WHERE MemberID=" & Me.MemberID)
> 
>         ' Select the hobbies currently on record for this MemberID.
>         With Me.lstHobbies
>             Do Until rs.EOF
>                 For intI = 0 To (.ListCount - 1)
>                     If .ItemData(intI) = CStr(rs!HobbyID) Then
>                         .Selected(intI) = True
>                         Exit For
>                     End If
>                 Next intI
>                 rs.MoveNext
>             Loop
>             rs.Close
>             Set rs = Nothing
>         End With
> 
>     End If
> 
> End Sub
> 
> Private Sub lstHobbies_AfterUpdate()
> 
>     On Error GoTo Err_lstHobbies_AfterUpdate
> 
>     Dim db As DAO.Database
>     Dim ws As DAO.Workspace
>     Dim strSQL As String
>     Dim blnInTransaction As Boolean
>     Dim varItem As Variant
> 
>     ' Make sure the current member record has been saved.
>     If Me.Dirty Then Me.Dirty = False
> 
>     Set ws = Workspaces(0)
>     Set db = ws.Databases(0)
> 
>     ws.BeginTrans
>     blnInTransaction = True
> 
>     ' Delete all hobbies now on record.
>     strSQL = "DELETE FROM tblFamilyMembersHobbies " & _
>                 "WHERE Memberid = " & Me.MemberID
> 
>     db.Execute strSQL, dbFailOnError
> 
>     ' Add each hobby selected in the list box.
>     With Me.lstHobbies
>         For Each varItem In .ItemsSelected
>             strSQL = _
>                 "INSERT INTO tblFamilyMembersHobbies " & _
>                     "(MemberID, HobbyID) VALUES (" & _
>                     Me.MemberID & ", " & .ItemData(varItem) & ")"
>             db.Execute strSQL, dbFailOnError
>         Next varItem
>     End With
> 
>     ws.CommitTrans
>     blnInTransaction = False
> 
> Exit_lstHobbies_AfterUpdate:
>     Set db = Nothing
>     Set ws = Nothing
>     Exit Sub
> 
> Err_lstHobbies_AfterUpdate:
>     MsgBox "Error " & Err.Number & ": " & Err.Description, _
>         vbExclamation, "Unable to Update"
>     If blnInTransaction Then
>         ws.Rollback
>         blnInTransaction = False
>     End If
>     Resume Exit_lstHobbies_AfterUpdate
> 
> End Sub
> 
> Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)
> 
>     Dim intI As Integer
> 
>     ' Don't allow hobbies to be updated before a MemberID has
>     ' been generated.
>     If IsNull(Me.MemberID) Then
>         MsgBox "Please enter other information for this family " & _
>             "member before choosing hobbies.", , _
>             "Define Member First"
>         Cancel = True
>         Me.lstHobbies.Undo
>         ' Clear the user's selection.
>         ClearHobbySelections
>     End If
> 
> End Sub
> '----- end of code ----- 
> 
> As you see, there's a fair amount of code involved, because we're using
> the list box to do something it wasn't built to do, but it works quite
> nicely.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
0
Utf
3/19/2008 5:15:01 PM
"sparlaman" <sparlaman@discussions.microsoft.com> wrote in message 
news:3E98D486-4C96-4073-9322-DCA90D59DA77@microsoft.com...
>I can see by your answer that my question was not clear. I do understand 
>the
> principles of a relational database and I am not trying to store multiple
> items in a single field.
>
> I am trying to use form controls i.e. checkboxes and multiselect list 
> boxes
> to populate  multiple records from a related table. So let me try to 
> explain
> again... I have a table "teachers_to_subjects" where each record contains 
> one
> teacher & one subject. It is in this manner that I am relating teachers to
> subject in a many-to-many fashion. What I want is a form control that will
> allow a user to select subjects per teacher thereby populating my
> "teachers_to_subjects" table.
>
> If you understand what I mean and can help me accomplish this. I'll 
> greatly
> appreciate it.


I don't think you read and understood my entire post.  That's exactly what I 
provided code to do.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
3/19/2008 8:09:15 PM
As Dirk said below, the most common way to enter 1:Many related data is with 
a subform for the TeachersToSubjects, where each row in the subform is one 
subject that's being taught by the current teacher on the main form. You 
enter a new subject for the teacher by choosing the desired subject from the 
combo box in the subform. No code is needed, just assigning the correct 
properties to the subform control when it's dropped on the main form.

If you really want to use a multi-select list box instead of a subform, 
Dirk's code is doing that. It's a lot of code because it's not the way 
Access imagined you would use a list box.

"sparlaman" <sparlaman@discussions.microsoft.com> wrote in message 
news:3E98D486-4C96-4073-9322-DCA90D59DA77@microsoft.com...
>I can see by your answer that my question was not clear. I do understand 
>the
> principles of a relational database and I am not trying to store multiple
> items in a single field.
>
> I am trying to use form controls i.e. checkboxes and multiselect list 
> boxes
> to populate  multiple records from a related table. So let me try to 
> explain
> again... I have a table "teachers_to_subjects" where each record contains 
> one
> teacher & one subject. It is in this manner that I am relating teachers to
> subject in a many-to-many fashion. What I want is a form control that will
> allow a user to select subjects per teacher thereby populating my
> "teachers_to_subjects" table.
>
> If you understand what I mean and can help me accomplish this. I'll 
> greatly
> appreciate it.
>
> Thanks,
> Sparlaman
>
>
> "Dirk Goldgar" wrote:
>
>> "sparlaman" <sparlaman@discussions.microsoft.com> wrote in message
>> news:54499235-B28F-42B5-A62B-8E6A6721E743@microsoft.com...
>> > I've read through some of the posts here and I just can't quite 
>> > understand
>> > how to do this. I have 3 tables: teachers, subjects, &
>> > subjects_to_teachers.
>> > I have relationships established for a many to many relationship btwn
>> > subjects & teachers. I have the base form which shows teacher info. How 
>> > do
>> > I
>> > create a control on this form that allows the user to select all 
>> > subjects
>> > that apply to a teacher? I would like to use check boxes where the user
>> > checks all that apply but I'm also interested in knowing how to use a
>> > multiselect list box to do the same.
>>
>>
>> I've used checkboxes on a subform, but found it cumbersome and fragile.
>> I've used a multiselect list box effectively, like this:
>>
>> *** Storing Multiple Selections From A List Box ***
>>
>> The best way to store multiple items in a single field is not to do it
>> at all.  Instead, use multiple records in a related table to represent
>> these items.  It's a principle of relational database design that a
>> single field (column) holds only one datum.
>>
>> An Access subform is designed to display and edit multiple records from
>> a related table (these records being related to the record currently
>> displayed on the main form), and does it with no code at all.  A list
>> box isn't designed to do this, but for small "pick-lists" I do like the
>> multiselect list box.  However, you need to use code to read the related
>> records for each new main record and select the appropriate items in the
>> list box, and then whenever the list box is updated you need to use code
>> to update the set of records in the related table.
>>
>>
>> Here's code from a sample form that represents "family members", with a
>> list box named "lstHobbies" that represents, for each family member,
>> that person's hobbies from the list of all possible hobbies.
>>
>>
>> '----- start of code ----- 
>> Option Compare Database
>> Option Explicit
>>
>> Private Sub ClearHobbySelections()
>>
>>     Dim intI As Integer
>>
>>     With Me.lstHobbies
>>         For intI = (.ItemsSelected.Count - 1) To 0 Step -1
>>             .Selected(.ItemsSelected(intI)) = False
>>         Next intI
>>     End With
>>
>> End Sub
>>
>> Private Sub Form_Current()
>>
>>     Dim rs As DAO.Recordset
>>     Dim intI As Integer
>>
>>     ' Clear all currently selected hobbies.
>>     ClearHobbySelections
>>
>>     If Not Me.NewRecord Then
>>
>>         Set rs = CurrentDb.OpenRecordset( _
>>             "SELECT HobbyID FROM tblFamilyMembersHobbies " & _
>>                 "WHERE MemberID=" & Me.MemberID)
>>
>>         ' Select the hobbies currently on record for this MemberID.
>>         With Me.lstHobbies
>>             Do Until rs.EOF
>>                 For intI = 0 To (.ListCount - 1)
>>                     If .ItemData(intI) = CStr(rs!HobbyID) Then
>>                         .Selected(intI) = True
>>                         Exit For
>>                     End If
>>                 Next intI
>>                 rs.MoveNext
>>             Loop
>>             rs.Close
>>             Set rs = Nothing
>>         End With
>>
>>     End If
>>
>> End Sub
>>
>> Private Sub lstHobbies_AfterUpdate()
>>
>>     On Error GoTo Err_lstHobbies_AfterUpdate
>>
>>     Dim db As DAO.Database
>>     Dim ws As DAO.Workspace
>>     Dim strSQL As String
>>     Dim blnInTransaction As Boolean
>>     Dim varItem As Variant
>>
>>     ' Make sure the current member record has been saved.
>>     If Me.Dirty Then Me.Dirty = False
>>
>>     Set ws = Workspaces(0)
>>     Set db = ws.Databases(0)
>>
>>     ws.BeginTrans
>>     blnInTransaction = True
>>
>>     ' Delete all hobbies now on record.
>>     strSQL = "DELETE FROM tblFamilyMembersHobbies " & _
>>                 "WHERE Memberid = " & Me.MemberID
>>
>>     db.Execute strSQL, dbFailOnError
>>
>>     ' Add each hobby selected in the list box.
>>     With Me.lstHobbies
>>         For Each varItem In .ItemsSelected
>>             strSQL = _
>>                 "INSERT INTO tblFamilyMembersHobbies " & _
>>                     "(MemberID, HobbyID) VALUES (" & _
>>                     Me.MemberID & ", " & .ItemData(varItem) & ")"
>>             db.Execute strSQL, dbFailOnError
>>         Next varItem
>>     End With
>>
>>     ws.CommitTrans
>>     blnInTransaction = False
>>
>> Exit_lstHobbies_AfterUpdate:
>>     Set db = Nothing
>>     Set ws = Nothing
>>     Exit Sub
>>
>> Err_lstHobbies_AfterUpdate:
>>     MsgBox "Error " & Err.Number & ": " & Err.Description, _
>>         vbExclamation, "Unable to Update"
>>     If blnInTransaction Then
>>         ws.Rollback
>>         blnInTransaction = False
>>     End If
>>     Resume Exit_lstHobbies_AfterUpdate
>>
>> End Sub
>>
>> Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)
>>
>>     Dim intI As Integer
>>
>>     ' Don't allow hobbies to be updated before a MemberID has
>>     ' been generated.
>>     If IsNull(Me.MemberID) Then
>>         MsgBox "Please enter other information for this family " & _
>>             "member before choosing hobbies.", , _
>>             "Define Member First"
>>         Cancel = True
>>         Me.lstHobbies.Undo
>>         ' Clear the user's selection.
>>         ClearHobbySelections
>>     End If
>>
>> End Sub
>> '----- end of code ----- 
>>
>> As you see, there's a fair amount of code involved, because we're using
>> the list box to do something it wasn't built to do, but it works quite
>> nicely.
>>
>> -- 
>> Dirk Goldgar, MS Access MVP
>> www.datagnostics.com
>>
>> (please reply to the newsgroup)
>> 

0
Paul
3/20/2008 12:50:06 AM
Reply:

Similar Artilces:

check mark colour
Is there any way to change the colour of the check mark in a checkbox? Nightshade wrote: > Is there any way to change the colour of the check mark in a checkbox? Sorry, no, not with a single form field. If you use the technique at http://gregmaxey.mvps.org/Add_Toggle_Objects.htm to make fake "checkboxes", and use an EQ field to make the checked box from two separate characters, you might be able to make the box black and the check mark another color. I haven't tried it. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email ca...

"All Users" address list secutiry settings problem
I added "deny" to "Everyone" for the "All Users" address list. Now I can't see the object in the Exchange System Manager. I can see the object in the Outlook Address book list. I can also see the object in ADSIEdit only it does not appear in the left pane as All Groups, etc. do. It appears in the right pane only when I select the All Address Lists node in the left pane and it has a document icon rather than the folder icon the other objects have. When I attempt to open its properties the cpu usage gets pegged and I get an error "an invalid pa...

Printing distribution lists on 1 page instead of 2
My office just upgraded to Outlook 2007. In our previous version of Outlook, when you printed a distribution list, it printed on 1 page (provided all the contacts fit on one page), with the name of the distribution list on top. Now, when I try to print the same distribution list, it prints the name of the distribution list on 1 page and the contacts on the second page. Is there any way to fix this? It is fairly inconvenient. I have looked in the printing options to no avail. There is no fix. It's a new feature. -- Russ Valentine "Kathy Dake" <Kathy Dake@di...

check left mouse button state anywhere on the screen
I think none of the WM messages let me check the mouse button outside the application window. If anyone knows how to do it, please let me know how. Thank you. "wanwan" <ericwan78@yahoo.com> ha scritto nel messaggio news:1192207261.783327.189720@i13g2000prf.googlegroups.com... >I think none of the WM messages let me check the mouse button outside > the application window. Have you tried SetCapture/ReleaseCapture? http://msdn2.microsoft.com/en-us/library/ms646262.aspx Giovanni To do so, you need to do a SetCapture(). -SM SetCapture is not really good for clic...

How do you sort a multi-column list by rows?
I'm a worship leader, I need to create an index for the songs in my notebook. I'm using Excel (I'm an absolute beginner, just got it yesterday) and need to know how to sort a list with several columns (song title, tempo etc.) by any one of the columns and keep the rows intact. Thanks 4 the help. If I understood your problem correctly, this should help, at least a bit. Select ONE cell in the column you want to sort by. Then click on a button in the toolbar, it should say AZ and an arrow downwards. To do an ascending sort, click the button with and arrow upwards. If you don&...

Search a worksheet, extract rows using a list from another sheet
I am trying to use a list of names (first, last) in a colum in worksheetA to find corresponding entries in an imported worksheet B, Once I find the name in worksheet B I want to extract the entire row of data into another new worksheet. For example Worksheet A: Column A Bill Jones Fred Smith Worksheet B: Place Overall Name Swim Bike Run Finish 1 12 Bill Jones 24 anytown ST 13:45 54:45 23:00 1:34:45 4 78 Fred Smith 56 anothertown ST 15:00 56:12 24:34 1:48:34...

List of built in functions
Does anyone know where I can get a list of all built in functions in access along with a description of usage? On Mon, 21 Jan 2008 08:41:01 -0800, juliejg1 wrote: > Does anyone know where I can get a list of all built in functions in access > along with a description of usage? Did you look in VBA help? Search for function -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Your success with this will vary with the version of Access. A2007 will give the best results and A2003 has been updated also as long as you allow help to search the web. AXP and...

changing contacts frm global address list to contacts in outlook 2003
This is a multi-part message in MIME format. ------=_NextPart_000_0024_01C7197D.17870700 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable How do I change in outlook 2003 so that the first place when I address a = email it looks at my contacts instead of my global address list. I'm = using the office Microsoft exchange server version. ------=_NextPart_000_0024_01C7197D.17870700 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Trans...

Drop Down Lists #2
Hello how do i create a drop down list in a cell? I can create a list but cant figure out how to create a drop down list You can use Data Validation. There are instructions in Excel's Help, and here: http://www.contextures.com/xlDataVal01.html Kobus wrote: > Hello how do i create a drop down list in a cell? > I can create a list but cant figure out how to create a drop down list -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html Thanks Debra that was quick "Debra Dalgleish" wrote: > You can use Data Validation. There are instructions in E...

Generating Data based on a List
Hi all, Just wondering if someone can help me. I have Sheet A with Col A with Town Names... and Col B with the postcode. there is about 150 towns. I then have another sheet... Sheet B... which has about 20 rows of information...some of which grabs information the contents of A1 and B1 from Sheet B.... into functions. That works fine. However I dont want to have to enter the 150 A1 and B1 items manuelly... and then have to save them as text files manuelly each time.... Basically If i could have it somehow read the values from Sheet A and put them into Sheet B... one by one.. it would then ...

franco to check e-mail
...

Checking for two values
Hi, sorry if this is a really easy question but it's driving me nuts because nothing I've tried seems to work. Basically, I am looking for a way to do this: If cell A1 contains the word "email" and cell B1 contains "@", give the result 1, otherwise give the result 0. I have tried using the AND function and wildcards, but I either get an error message or it returns an incorrect result. Can anyone help please? Thanks very much Terry One way: =--and(countif(a1,"*email*")>0,countif(b1,"*@*")>0) =and() will return True or False. The -- s...

Creating Price Lists and Products
Tell if it is impossible to create a product catalog in this order according to these instructions. Creating the Product Catalog The Microsoft CRM product catalog is the central location for storing product information. The product catalog is a collection of information about your organization's products and services. The items in the product catalog are created or managed through the Settings area of Microsoft CRM. Additionally, you can view the product catalog either online, when you are connected to your corporate network, or offline when you are at a customer's site by usi...

Open dialog box.
I have looked at the code at http://www.mvps.org/access/api/api0001.htm......however am unsure how to use it. It's a lot of code, but what and where do I paste it? I want an open dialog box to come up when I click a certain button, so the user can choose the CSV file that will be imported to a table. What is the easiest way to do this? 1.Create a new module and paste the code from the MVPS website 2.Create a button on your form and select to create a click event (using code). they provided a Testit function which illustrates how to call it from anywhere. Function TestIt() Dim s...

Bank balance issue on Account List page
Just noticed after converting to '08 that two accounts are showing Zero (0) Adj Balance but the Bank Balance column show a negative entry. I viewed each account and confirmed that the starting balance was 0 and the current balance was 0 so not sure why these two accounts are showing up having a negative Bank Balance. I also confirmed that at least one account was Balanced to the bank but that didn't fix the issue. Besides a possible repair fixing the issue, is there another solution or something else I didn't do? Thanks In microsoft.public.money, Geekster wrote: >...

Check 3 cells for specific content
Hello, Is it possible to use code to check the value of 3 different cells at the same time for specific content? Example: A1 should = Part Number B1 should = Order Quantity C1 should = Order Date If the spreadsheet doesn't have this cell content in thid location, Then MsgBox '"Wrong File" Your help is appreciated so much! Hi in VBA try sub foo() dim str with activesheet str=.range("A1").value & .range("B1").value & .range("C1").value end with if lcase(str)<>"part numberorder quantityorder date" then msgbox &...

Sorting for Specific words/ numbers from list
Hi Folks; I am trying to figure out a way to sort some information from data tha appears in a worksheet via an import. The import works like straightforward paste function from another program (in this case Quickbooks). The information pastes the same every time, i.e. dat appears in the same columns every time. These columns have a name in column (ex. A1)and a monetary value in another column (ex. D1). Wha I'm having to try and do is make a formula that will sort similar name and sum up the monetary values that match up with them. The "simila names" are actually numeric codes...

Customizing 3.0 Marketing List Members View
When I open a Marketing List and click on Members, a view of the lists members is displayed. I would like to add a column to the list from the Contact record. Is this possible? Thanks I'm trying to do the same thing. If I use Advanced Find to create my list (from leads in my case), it allows me to select columns. However, when my Marketing List Members list is displayed, the columns appear fixed, showing Name, Business Phone, and City. I can't find anywhere in the entities to change this. It's not very useful as is. "Rocco P" wrote: > When I open a Marke...

List of fonts required to be enabled for Office 2004?
QUESTION Where might I find a list of fonts that Office 2004 requires to be enabled? BACKGROUND: I used Tiger's Font Book to disable several Asian and other font families, but avoided disabling any that Font Book dialogues said were needed for the System. Later, when I tried changing fonts within Entourage messages or Notes, before any other fonts than the one displayed could show up, Microsoft Report came on and it and Entourage went into spinning beachball mode. A subsequent force-quit showed that both were "not responding." When even a rebuild of the Entourage databa...

Rules Wizard List
It seems that the 'list' of contacts that I want to receive e-mail from grows and grows and grows. You can't sort it but I've discovered multiple addresses when I've only added them once. Any suggestions or answers as to why this occurs? ...

Forms List box relations
How do I relate two list boxes on a form? I want to populate one list box automatically depending on the first list box value or selection. ie. When I select a PersonID ın the first list box I want the second box to populate the name automatically that is related to the selected ID. You could try using a query as a source for your second listbox. Set the parameter (PersonID) to the first listbox. You'd have to do a requery after you click on the first listbox to get the results in the second listbox though. hth -- Maurice Ausum "EWAPOOS" wrote: > How do I relate t...

Distribution list
Is it possible to sort the names in a distribution list by last name? Thanks, John Pinback John Pinback <pinback1752@yahoo.com> wrote: > Is it possible to sort the names in a distribution list by last name? The DL "Name" field reflects the "Display As" field of the added contacts. If you specify the Display As fields the way you want (i.e., "Last, First"), then the Name field in the DL will be alphabetized by last name. You can resort either ascending or descending by clicking the Name column header. When you are in the process of choosing the...

How to show dropdown list from another workbook without running it
I created a dropdown list using as a source a column from another spreadsheet. When I colose the source spreadsheet the dropdown list doesn't work anymore. Any idea? Copy the choices for the drop down to the main workbook.. If you don't like seeing them in the main workboook, put them on another worksheet and hide that worksheet.. "Kuche" wrote: > I created a dropdown list using as a source a column from another > spreadsheet. When I colose the source spreadsheet the dropdown list doesn't > work anymore. > Any idea? ...

comparing check box values to a table
Hi, Hope you can help, I have a worksheet with five check buttons on it Depending on the combination of buttons selected I want to select value for use elsewhere in the sheet. I've built a table with five columns which has every possible logica combination but don't know how to get my check button link cells t look at the table. compare them and select the result. I've attached the file thanks to anyone who helps Mar -- Message posted from http://www.ExcelForum.com Mark, A suggestion. If checkbox 1 is clicked, add/subtract 1 from a global variable If checkbox 2 is clicke...

How to check client version
Hi, Is there a way to check which version out the Outlook client the users a running without having to ask every single user? I have added the Rollup5 update to the database, so the users can download/update it manually, but I'm wondering if there is a way to find out how many of them has actually applied the RollUp. If it was stored in the database and synced back to the server it would be quite easy to find out, but I haven't been able to find such a field yet. -- Regards Steen Schl�ter Persson (DK) ...