Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combo boxes (Access 2000 SR1)

    One more question: Is there a way to set a form combobox to NO for Limit to List and then have new entries stored in the lookup table rather that the underlying table that the form is representing?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combo boxes (Access 2000 SR1)

    Yes, that is possible. If you do a search for NotInList in this forum, you'll find lots of examples, for instance in <post#=159332>post 159332</post#>.

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo boxes (Access 2000 SR1)

    Thank you, I'll do that.

  4. #4
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo boxes (Access 2000 SR1)

    Hans,
    I tried the Event Procedure you pointed out to me, substituting the names of my Combo box and my table, but I get an syntax error message for the "INSERT INTO" line. Below is the Event Procdure as I entered it. Can you see any mistakes.

    I started this db before I was aware of the convention for naming tables, forms, boxes, etc. with a prefix indicating the nature of the beast (tbl, frm, cbo, mcr, etc.)

    My lookup table is named Physicians. My combo box in the form is named Reading Physician, control source ReadingPhysician.

    I assume the problem is with the string in parentheses following (Reading Physician), but I don't know what it should be. From what I've read it should indicate the fields in the table I want the NewData to be entered into (which is 2 columns: 1 - autonumber "phID" and 2 "phName"). I have tried various combinations within the ( ) to no avail.


    Private Sub Reading_Physician_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    If MsgBox("The type " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add it?@", vbYesNo + vbQuestion) = vbYes Then
    strSQL = "INSERT INTO Physicians (Reading Physician) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    Reading_Physician.Undo
    Response = acDataErrContinue
    End If

    End Sub



    And, Please fogive me, but one last question:

    I would like to set the SocialSecurityNumber field in my data entry form, to flash a warning and refuse entry of a duplicate number. I'm sure that a VBA foutine that searches the database, etc. etc. will perform this function.

    However, I am a complete novice with Access and have obviously gotten way over my head in this project. I have tried to read a book about VB and I just do not understand it.

    I promise not to bother you all again with this probably really simple stuff.

    Thank you so much for your patience. (If I decide to pursue this any further, I intend to look for a local Community Colleg that mya offer courses.)

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combo boxes (Access 2000 SR1)

    I think one of your problems is that your field name (based on the control source for the combo) is "ReadingPhysician" with no space between the words. When I do this sort of thing, I typically test it by pasting the SQL String into the query tool to check and see if I have the syntax correct. Hope this helps.
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: combo boxes (Access 2000 SR1)

    One of your commands is:
    strSQL = "INSERT INTO Physicians (Reading Physician) VALUES (" & _
    Try changing it to:
    strSQL = "INSERT INTO Physicians ([Reading Physician]) VALUES (" & _

    Note the square brackets around Reading Physician.

    You wrote:
    <<I would like to set the SocialSecurityNumber field in my data entry form, to flash a warning and refuse entry of a duplicate number. I'm sure that a VBA foutine that searches the database, etc. etc. will perform this function.>>
    You could put the following code in the BeforeUpdate event of the SocialSecurityNumber field.
    Dim strSQL as string, dbs as DAO.Database, rs as DAO.Recordset
    Set dbs = CurrentDB
    strSQL = "SELECT * FROM Tablename WHERE SocialSecurityNumber = '" & Me!txtSocialSecurityNumber & "'"
    Set rs = dbs.OpenRecordset(strSQL)
    If Not rs.EOF then
    MsgBox "SocialSecurityNumber already exists !! Try entering another number"
    Cancel = True
    End If
    Set rs = Nothing
    Set dbs = Nothing

    The above code assumes the Social Security Number is text, if not take out the single quotes.

    You wrote:
    <<However, I am a complete novice with Access and have obviously gotten way over my head in this project. I have tried to read a book about VB and I just do not understand it.

    I promise not to bother you all again with this probably really simple stuff.>>

    This really simple stuff that you say doesn't take long to show you the way, it's the complex stuff that takes the time. You will find that most of us have been where you are at and it does take time to learn, so don't worry about it, I don't.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo boxes (Access 2000 SR1)

    Got it! It was the field name of the table in parentheses that was wrong.
    I will try the SS# code next.
    Thank you all for your help. You are very kind.

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo boxes (Access 2000 SR1)

    I tried your suggested code, substituting my tablename for "tablename". Received a Compile error message. Is there something else in the code that i am supposed to replace with a name?

    Produces Error Message:
    Compile Error
    User-defined type not defined.

    dbs As DAO.Database is highlighted in the Debug screen
    Also first line is highlighted in yellow in the Debug screen

    Private Sub SocialSecurityNumber_BeforeUpdate(Cancel As Integer)
    Dim strSQL As String, dbs As DAO.Database, rs As DAO.Recordset
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Patients WHERE SocialSecurityNumber = '" & Me!txtSocialSecurityNumber & "'"
    Set rs = dbs.OpenRecordset(strSQL)
    If Not rs.EOF Then
    MsgBox "SocialSecurityNumber already exists !! Try entering another number"
    Cancel = True
    End If
    Set rs = Nothing
    Set dbs = Nothing

    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combo boxes (Access 2000 SR1)

    Pat's code uses DAO. Open any module, and select Tools/References... Locate Microsoft DAO ... Object Library in the list and check its box, then click OK. Hopefully the code works now.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: combo boxes (Access 2000 SR1)

    Have you set the reference of the Microsoft DAO 3.6 Object Library.
    To set this reference just go into any code and click on Tools/References and set the reference I mentioned, then try the code again.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    New Lounger
    Join Date
    Oct 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo boxes (Access 2000 SR1)

    thanks guys,

    It works great now. You're the best!!

Posting Permissions

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