Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    In another thread, John Hutchison gave me this code for the NotInList event of combo boxes (it takes what I typed in the combo box, and if it is not in the list of entries for that field, offers to add it). When it adds that new entry to the list it creates a new record.

    This works fine as long as this only comes up once on a form. But, if it comes up two or more times, say if I have a combo box for last names, it will create one new record when I add a new first name to the list, and a second new record when I add a new last name to the list.

    My form does not do this if I choose a first name from the drop down list in its combo box, and a last name from the drop down name in its combo box.

    I can see why this is happening (the rs.AddNew execute every time the function runs), but I'm not sharp enough yet to get that line to execute only when the record is actually new.

    Private Sub cboFirstName_NotInList(NewData As String, Response As Integer)
    If fnAddRecord("tblPersons", "firstName", "first names", NewData) = True Then
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    End Sub

    Public Function fnAddRecord(strTable As String, strfield As String, strtitle As String, NewData As String) As Boolean
    Dim newEntry As Integer, Title As String, msgDialog As Integer
    Const MB_YESNO = 4
    Const MB_ICONEXCLAMATION = 48
    Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7



    Title = strtitle & " not in list"
    msgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
    newEntry = MsgBox(NewData & " is not in the list of " & strtitle & ". Do you want to add a new entry to the list ?", msgDialog, Title)
    If newEntry = IDYES Then
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Set db = CurrentDb
    sql = "Select * from " & strTable
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    rs.AddNew
    rs(strfield) = StrConv(NewData, 3)
    rs.Update
    rs.Close

    Set db = Nothing
    Set rs = Nothing
    fnAddRecord = True
    Else
    fnAddRecord = False
    End If

    End Function

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Does your form have two combo boxes that draw data from the same table?

    My code example, which was the simplest of many versions, is really only applicable where the lookup table has only one field of relevance. If you are adding people to a list, you should add first and lastname at the same time.
    Also if you are choosing people with a combo, you would usually use just one combo box, bound to a personID field, but displays first and last names concatenated. So it would have two columns, but the first column width would be 0, and the second column would display Person: [firstname] & " " & [lastname].

    An On Not in List event in that case would be more complicated. It would have to split the newdata into two fields (splitting at the space) then create the new record automatically, or open a form with the fields filled in so you could check the data before adding the new record.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    1) Yes, my form has multiple combo boxes that point to the same table.

    2)

    My code example, which was the simplest of many versions, is really only applicable where the lookup table has only one field of relevance.

    Agreed.

    If you are adding people to a list, you should add first and lastname at the same time.
    Also if you are choosing people with a combo, you would usually use just one combo box, bound to a personID field, but displays first and last names concatenated. So it would have two columns, but the first column width would be 0, and the second column would display Person: [firstname] & " " & [lastname].

    Perhaps so. I tried to program this but could not get it to work. I am also not sure if this makes the most sense if I have a secretary entering data - I was thinking it would be more straightforward for a novice to put a first name in one (combo) box and a last name in another (combo) box. The table is now organized with firstName and lastName as different fields, and I'd rather not have to change that.

    3) I am not following your last paragraph. Does it refer to your suggestion from the second paragraph, or to your understanding of how my tables are set up from your first paragraph? Either way, I'm confused by it.


    I am not sure that my trouble is not from applying how I would do this in other languages that I know better. To me, it seems like the easy solution is to pull the creation of the new record out into an if/else that captures whether or not I just created a record. But I think I am going down the wrong road in VBA if I try to do it this way.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When you connect tables you should always connect via the primary keys. So if you have a table of people (tblpeople with personID as they key), any other tables that refer to people should only store the personID. I have done this hundreds of times, then used a single combo to choose the person.

    If you use two combo boxes you could easily choose a combination of first and lastnames that do not belong any real person. To avoid this you would need to restrict the second combo to values consistent with what was selected in the first.

    My last paragraph referred to a situation where the user has typed "John Brown" into a combo, and you want the VBA to split this into John and Brown, create a new person record and put John into firstname and Brown into lastname.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I think my db is in line with your methodology.

    The problem I am having is that my data entry person is finding my data entry process to be kludgy. I am about 2 weeks out from having to deal with more than 1 part time data entry person instead of 1 full time person. I am taking the full time person's usability suggestions very seriously in preparation for that.

    My form does refer to a single personID from tblPersons. But the people's names are broken into different fields: firstName, middleName, and so on.

    I want the ability to create a new person's record by choosing from the lists in the combo boxes - I have a lot of shared first and last names.* This seemed to me like the best way to get my data entry person to reduce errors. Perhaps this was a bad way to go.

    But, I also want the data entry person to be able to add new people whose names are not in the fields yet, and so are not populated to the combo boxes. My data entry person likes being able to use the drop down list to see if the "new" person really is new. A data entry method based on the "List Items Edit Form" property works fine, but my data entry person doesn't like this because they have to type the data into the combo box before it fires, and the retype it again in the form that pops open. I tend to agree that there ought to be a better way to do this. Perhaps my solution is really a set of directions on my form that says check the drop-down list before you start typing?

    Alternatively, I could probably do what we want with the following: a combo box and a text box side-by-side, using the combo box only to look up whether someone is already on the list and make a choice if they are, and a text box to allow an addition to the list if the name isn't in the combo box. This seems inefficient to me, and error-prone, but maybe I'm wrong.

    * Where I'm going with this is that I have external sources that I have to exchange information with that might call me "David", "David Ralph", "D.", "D.R.", and "David R.". Each of those might be connected to projects in a many-to-many junction table. For each person I have this problem, so my tblPersons has a unique ID for each person with a single preferred formal name, that I connect with a one-to-many where all their aliases are on the many side, and then I connect that to a many-to-many which matches projects to the specific alias that is listed on its title page. I didn't invent all the aliases, but I do have to produce reports that combine them all into one person, and different reports that list them as if they were separate people.

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    It also occurred to me that the best way to go might be to have the "List Items Edit Form" set to be the same thing for each of the combo boxes (cboFirst, cboMiddle, cboLast). That way if the data entry person types into one of the boxes and fires the edit form, they have to fill out all the fields on the edit form, and then when they go back to the combo boxes on the original form they're already populated. Is this how this is supposed to work?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you do have a single personID field then how is that populated?
    If it is attached to any of the combo boxes (and there now seem to be three) then once you choose from the first combo, the other two combos are redundant.

    For people I tend not to use "Not in list events" because:
    • Just because someone's name is in the list does not mean it is actually the same person. There can be two different John Browns.
    • You need to make a decision based on more data.
    • When you add a new person, I usually want to add a lot of extra data such as address, phone numbers , email etc.
    One solution is to use unbound controls for the user to enter a firstname and lastname, then do a search to see if anyone matches, then act on the results of the search.
    Regards
    John



  8. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    The issues are expanding ...

    1) I think you're first 2 paragraphs have unearthed a problem. When I change the record at the bottom of the form, all the combo boxes update to match, but when I change the datum in one of the combo boxes, the other two don't update. What do you mean by "attached".

    2) I actually have 2 classes of people: those that I need a lot of info for, and those that I just need a name. Perhaps I should use a List Items Edit Form.

    3) I think we are on the same page about an unbound control. Do you mean to have an unbound control to look up whether someone is in the DB, and a bound one to add new people?

  9. #9
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I have gone with the unbound control to check whether someone is in the database, and a button to a form based on text boxes that is purely for adding new people. It works (but I still feel it is kludgy).

    I am wondering if my problems are because I am working at cross-purposes. I need to do 2 things: 1) get existing data into the DB with some degree of normalization and recombine it, and 2) allow incremental additions of data.

    Is my problem really that I am trying to use combo boxes on single forms to do both tasks, instead of creating one set of forms for objective 1 with mostly combo boxes, and a second set of forms for objective 2 with mostly text boxes?

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In some situations I don't want users to be able to create new people..(there is another process for this)...in these cases I provide a single combo bound to an ID field, with a hidden first column, and the first name and lastname concatenated in the visible column. Depending on the circumstance, I display this is John Brown or Brown, John. I think the second option is better if the list of people is long.

    However, if I was entering a sale then the customer may be an existing customer or a new one. Here I provide a popup form with two unbound controls.
    [attachment=89594:entername.gif]


    Then a search is conducted.
    • If no match is found you are taken to a screen where you enter details of the new person. The names previously entered into the unbound form, are used to populate the relevant "new person" form.
    • If one or more matches is found, you see a list of matches, with more details displayed to see if any of the people is the one you want. You can then either choose one, or instead, decide you do need a new person. In that case you are directed to the new person form.
    At the end of the process the new person, or the person chosen from the list, is used as the person for the 'sale'.

    [attachment=89595:MatchingNames.gif]
    ********

    When you use 3 combo boxes, what are they bound to? By 'attached' I mean 'bound to". I don't understand what you do with the three combos, because I don't understand what fields you are using. The only person field I would have in the table is the PersonID field, so I don't know what to do with these combos.
    Attached Images Attached Images
    Regards
    John



  11. #11
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I'd be interested in the code that does the search you describe in the middle (plus as much info about what lies behind those 2 screenshots as seems worthwhile) - if you're willing to upload it.

    Other than that, I think I'm good - I've learned a lot from this thread (not what I initially set out to do, but perhaps how not to get into that jam in the first place).

  12. #12
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I'm back. I ruminated over this for a few days, and I think I'm getting a handle on the issues here: I see them better from a theoretical standpoint, but I'm having a lot of trouble seeing how the controls in Access interface with the theory.

    So, I've switched my forms around: one record source for everything feeding information into text boxes, and no combo boxes with potentially different record sources.*

    Now I have a methodological question. My form (frmPersons) has a single record source (a person in tblPersons). But, a field for that record is a foreign key leading to the one side of a relationship (many persons may belong to one job category from tblJob). What is the appropriate way to include a person's job category (in plain English rather than as the foreign key)? I can do this by adding information to a query underlying frmPersons. I can also do it by constructing a subform for tblJob, and including that subform on frmPersons. How do I decide which is better?

    * My original problem stems from taking something that worked from a subform - that did need combo boxes to restrict users to the data I wanted the data entry person to use - and applying it to the main form with a combo box that got me into trouble because I could give it a record source that was different than that of the overall form.

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Sorry but this is not clear to me. Can you show a Relationships diagram for the tables involved?
    Regards
    John



  14. #14
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    File attached.

    I'm currently working the bugs out of the top left.

    My form frmPersons applies to tblPersons. It has a subform that relates to the junction table at the bottom tblPersonDegreeField... That subform works fine, and it's a situation where a subform is probably the best choice.

    What I'm wondering with this last question is what about the tblDepartment at the top left. Its PK, departmentID is an FK in tblPersons. If I just build frmPersons from tblPersons, then frmPersons will show the value of that key (a number) rather than the name of the department.

    If all I want is for departmentName from tblDepartment to show up on frmPersons, it really doesn't seem worthwhile to build a subform for it. I can just put together a query that draws from tblPersons and tblDepartments and build frmPersons from that so that it will show departmentName rather than departmentID. This is more user friendly.

    What I'm wondering, is given the mistake I made at the top of the thread (which was a result of not thinking through how I was addressing problems like this), am I now on the right track? And, if so, where is the borderline between when I should use a query to put together data from two or more tables vs using a subform.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think you forgot the attachment.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •