add/update (Access 97, sr2)

Topic

New Reply

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

Reply | Quote

Replies

can we see the code you are using

Reply | Quote

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

Reply | Quote

>>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.

Reply | Quote

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.

Reply | Quote