Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add/update (Access 97, sr2)

    I am working on an Access 97 database to register students. I have created several forms. One for adding a new person and another for adding an existing person. The user wants to be able to do this at one time instead of using different forms. I have a combo box with a current list of students by SS#. The user keys in the SS# and on the after update event I check to see if there is a match. If there is no match, then I activate a new record and the user can enter the information. If there is a match, I activate a new record, automatically fill in the fields, and the save the record with the class information.

    I have one problem. It seems to create a blank record every time. I have to go back to the table periodically and delete all the blanks. It is also very slow. Any suggestions??? Can I do something different that will not add a blank and speed up the process?

    Thanks,

    Deborah

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add/update (Access 97, sr2)

    can we see the code you are using

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add/update (Access 97, sr2)

    I can't answer your question, but I have a suggestion to make it easier to debug. Use the required property on one or more of your fields (ones that should never be blank) to prevent blank records from being created. You'll get error messages, but that will give you a point to start debugging from.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add/update (Access 97, sr2)

    Here is my code - one word of warning....I am a novice in VBA!

    Sub Combo82_AfterUpdate()

    Dim rst As Recordset
    Dim mess As String
    Dim lname As String
    Dim fname As String
    Dim soc As String
    Dim addnew As String
    Dim citynew As String
    Dim statenew As String
    Dim birth As String, sx As String, zip As String, phnum As String
    Dim jc As String, cname As String, ca As String, cc As String
    Dim cs As String, cz As String, ccode As String
    Set rst = Me.RecordsetClone

    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[ss#] = " & Me![Combo82]
    Me.Bookmark = Me.RecordsetClone.Bookmark

    If rst.NoMatch = True Then GoTo NEWREC

    mess = MsgBox("Is this the correct person?", vbYesNo, "Search")

    If mess = 7 Then GoTo NEWREC

    'existing person

    If mess = 6 Then

    Let lname = Me![LastName]
    Let fname = Me![FirstName]
    Let soc = Me![Combo82]
    Let addnew = Me![Address]
    Let citynew = Me![City]
    statenew = Me![State]
    birth = Me![Birthday]
    sx = Me![Sex]
    zip = Me![Zipcode]
    phnum = Me![Phone]
    jc = Me![JobCode]
    cname = Me![Company Name]
    ca = Me![Company Address]
    cc = Me![Company city]
    cs = Me![Company State]
    cz = Me![Company zip]
    ccode = Me![Country Code]

    DoCmd.OpenForm "frmcalendar student no ss#", acNormal, , , acFormEdit
    DoCmd.GoToRecord , , acNewRec

    Let LastName = lname
    Let [FirstName] = fname
    Let [ss#] = soc
    Let [Address] = addnew
    Let [City] = citynew
    [Coursecat] = Forms![frmcalendar]![calendar/course subform]![Coursecat]
    [SubCode] = Forms![frmcalendar]![calendar/course subform]![SubCode]
    [CourseCode] = Forms![frmcalendar]![calendar/course subform]![CourseCode]
    [dateoffered] = Forms![frmcalendar]![calendar/course subform]![dateoffered]
    [Time] = Forms![frmcalendar]![calendar/course subform]![Time]
    [inst] = Forms![frmcalendar]![calendar/course subform]![Instructor]
    [State] = statenew
    [Birthday] = birth
    [Sex] = sx
    [Zipcode] = zip
    [Phone] = phnum
    [JobCode] = jc
    [Company Name] = cname
    [Company Address] = ca
    [Company city] = cc
    [Company State] = cs
    [Company zip] = cz
    [Country Code] = ccode

    End If
    GoTo FINISH

    'NEW Person


    NEWREC:


    DoCmd.GoToRecord , , acNewRec

    [Coursecat] = Forms![frmcalendar]![calendar/course subform]![Coursecat]
    [SubCode] = Forms![frmcalendar]![calendar/course subform]![SubCode]
    [CourseCode] = Forms![frmcalendar]![calendar/course subform]![CourseCode]
    [dateoffered] = Forms![frmcalendar]![calendar/course subform]![dateoffered]
    [Time] = Forms![frmcalendar]![calendar/course subform]![Time]
    [inst] = Forms![frmcalendar]![calendar/course subform]![Instructor]

    [ss#] = Me![Combo82]

    DoCmd.GoToControl "Firstname"




    FINISH:


    End Sub



    I know there are several times that I goto a new record. I have tried removing them but it does not work. I also have a Goto New Record when the form is opened.

    Hope you can understand my logic!!!

    Thanks for looking at it.
    Deborah

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: add/update (Access 97, sr2)

    >>Hope you can understand my logic!!!<<

    Not really, but it seems you are doing alot of work. First of all, it seems you are copying alot of information to the new record. Why? Also, it seems like it would be easier to do an insert query on the table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    LinguaGo
    Guest

    Re: add/update (Access 97, sr2)

    Hi,
    You say " ...I also have a Goto New Record when the form is opened.". If that is the case and you are sitting on a new record when you execute the bookmark instruction:
    Me.Bookmark = Me.RecordsetClone.Bookmark
    then the new record will be saved and becomes a blank record.
    Your next instruction to "Goto New record" opens up the record that you fill with data.
    The bookmark instruction should be moved down in the logic after "If mess = 6" since that is when you need to position on a matching record. The bookmark will still cause the new record to be saved. You will simply have fewer blank records. So I would recommend not firing this event when a new record is actively displayed (remove the Goto New record from the open event).

    That's my best guess concerning those blank records.

    Hope it helps.

    LinguaGo
    Internationalize your Access Applications
    www.linguago.com

Posting Permissions

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