Can a VB form be used to update an ACCESS autonumber type field?

I am not sure if this is a VB(6) question or an ACCESS(97) question. I have written a Visual Basic form to update an Access database.  The ID field in Access is a type AutoNumber.  When I update the database with the new record the ID is left blank.  Is there anyway to get autonumbering to work with a VB form?

Thanks for your help
goldenpawsAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>I take it that you don't care for DataControls, is there some other way to connect to a database?<<
The old fashioned way: writing code.  But that is not the issue here.

I switched to ADO from DAO over four years ago, so I am not really sure of the issue here. But try changing the DefaultCursorType to vbUseServerSideCursor

If this does not make any difference you may want to explain what ClientServiceQry means.

Anthony
0
 
schworakCommented:
Are you sure it is set to AutoNumber? If it is it will automatically number. Since you say it is left BLANK that tells me it is not auto number because number fields are not normally blank (null) they are usually zero by default.

In any case, this is an Access issue not something wrong with your VB code as long as that field is really set for autonumber.
0
 
goldenpawsAuthor Commented:
I double checked the database and the field is autonumber and there a number in the field.  But when I select the record to view in the VB form the form field in blank.  Any idea what could cause this?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Anthony PerkinsCommented:
Unfortunately, without more details or relevant code, the best we can do is make WAGs (Wild Ass Guesses) Here is one:

It is because your CusrorLocation is set to adUseServer, set your CursorLocation to adUseClient.

Anthony
0
 
schworakCommented:
Please if the database isn't massive (more than 10meg) email it to me and I will take a look. If you can zip it up with your entire VB project I would be happy to look it over really quick.

I can return it fixed and/or post the answer here. VB/Access problems like this can be a little quirky without knowing more about what you are trying to do.


glenn@schworak.com
0
 
goldenpawsAuthor Commented:
I have emailed you two zip files with the VB program and database files
0
 
Arthur_WoodCommented:
if by "blank" you mean that when, after updatng the record in your code, you then try to check the value of the ID field in the recordset, it does not appear to have been assigned a value, then that is VERY easily corrected.

My guess is that you are using DAO to open the recordset (Correct?)

If so, then immediately after the .Update call, then

rs.MoveLast
Id = rs.Fields("ID")


will then force the recordset to be "re-populated" from the underlying table, and this retrieve the NEW value of the ID field.

Arthur Woof
0
 
Anthony PerkinsCommented:
This has nothing to do with the database and everything to do with your code.  I can cite half a dozen articles regarding this problem, so please post relevant part of your code to narrow the problem down.

Anthony
0
 
schworakCommented:
Got the code/database. I am assuming the code is one of the modules behind the form. but you forgot to tell me which module you are having problems with.
0
 
schworakCommented:
Ok, I think I have it figured out but I need some more info from you. I am looking at the ServiceDetail table and it has several columns. ServiceType and TreatmentID seem to be the coluns of interest correct?

Service type holds the text of the service but Treatment ID shows ther number that goes along with the text in Service Type correct?

If this is correct, that is the problem. This is not a normal thing to do in a table. If you are going to store the TreatmentID in the table you would normally use that ID as a pointer to the Treatment table which in turn returns the text. It is not normal to store both the text and the ID in this manner. It can be done if that is really what you want but it requires a little tweek. Really simply, one line of code and one field added to your form.

If this is what you are really trying to do and you really want to do it that way let me know. If you want to do it the other way let me know that too and I will explain the proper method to get the results you are after.
0
 
goldenpawsAuthor Commented:
You are correct, what I want is to store the TreatmentID and not the text in ServiceDetail.  I want to use the TreatmentID as a pointer to the text.  
0
 
schworakCommented:
That is an easy one. Follow these steps....

This gives you a drop down list in the table when you are working it in raw table view. (not through your program) If you don't care about that you can skip these steps

1. Open the table ServiceDetails in design mode
2. Select the TreatmentID field
3. Select the LookUp tab at the bottom of the window
4. Set the Display Control to LIST
5. Set the Row Source Type to Table/Query
6. Set the Row Source to Treatments
7. Set Bound Column to 1
8. Set Column Count to 2
9. Set Column Heads to NO
10. Set Column Widths to 0,1 (this will hide the number and show the text)
11. Close and Save the table

This next part assumes you want to save your existing data. If you don't (if it is all test data) you can simply delete all the records in the ServiceDetail table and skip these steps.

1. Open the table ServiceDetails in edit mode
2. Select the proper service type in the TreatmentID field so it matches the text in the ServiceType field
3. Repeat this for all records
4. Close the table.


This is the part you need to do to get what you are after in your program and to make the database work properly.

1. Open the table ServiceDetails in design mode
2. Delete the field ServiceType
3. Close and save the table
4. Open the form ServiceDetail Subform in design mode
5. Select the ServiceType field and display the properties page
6. on the DATA tab set the control source to TreatmentID and the Row Source to Treatments
7. on the FORMAT tab set the column count to 2 and the column widths to 0,1
8. Close and save the form.




You are done
0
 
goldenpawsAuthor Commented:
I completed steps 1-11, deleted all the records in ServiceDetail, and did steps 1-8. And when I run the VB program I get the following error:
Too Few Parameters. Expected 1

Why would I receive this?
0
 
schworakCommented:
I am not sure what could have happened so I am sending you the updated version. to your email address.
0
 
goldenpawsAuthor Commented:
I have received the database you sent. The error with 'too few parameters' is gone, but when I use the VB program to access the new DB I still have the original problem. I don't see any client ID's. It can add new records, I refresh the DB, but it still only displays clientid 1,2,and 3. Does your show any client ID after 3?
0
 
Anthony PerkinsCommented:
Once again, post the relevant code.  The AutoNumber field showing up null is a well known problem, it has nothing to do with the database.

Anthony
0
 
goldenpawsAuthor Commented:
Here is the code for the Add/Change form:

Option Explicit
Private mrstClient As Recordset

Private Sub datClient_Reposition()
    Static pblnFirst As Boolean
    If pblnFirst = False Then
        Set mrstClient = datClient.Recordset
        pblnFirst = True
    End If
End Sub


Private Sub mnuEditAdd_Click()
    mrstClient.AddNew
    mnuEditAdd.Enabled = False
    mnuEditRecord.Enabled = False
    mnuEditDelete.Enabled = False
    mnuEditUpdate.Enabled = True
    mnuEditRefresh.Enabled = False
    mnuFind.Enabled = False
End Sub

Private Sub mnuEditRecord_Click()
    mrstClient.Edit
    mnuEditAdd.Enabled = False
    mnuEditRecord.Enabled = False
    mnuEditDelete.Enabled = False
    mnuEditUpdate.Enabled = False
    mnuEditRefresh.Enabled = False
    mnuFind.Enabled = False
End Sub

Private Sub mnuEditRefresh_Click()
    mrstClient.Requery
    mrstClient.MoveFirst
End Sub

Private Sub mnuEditUpdate_Click()
    mrstClient.Update
    mnuEditAdd.Enabled = True
    mnuEditRecord.Enabled = True
    mnuEditDelete.Enabled = True
    mnuEditUpdate.Enabled = False
    mnuEditRefresh.Enabled = True
    mnuFind.Enabled = True
End Sub

Private Sub mnuFileExit_Click()
    Unload frmAddChange
End Sub

Private Sub mnuFilePrint_Click()
    PrintForm
End Sub

Private Sub mnuFindLastName_Click()
    Dim pstrLastName As String
    pstrLastName = InputBox("Enter the Last name", "Find")
    If pstrLastName <> "" Then
        mrstClient.FindFirst "LastName = " & "'" & pstrLastName & "'"
        If mrstClient.NoMatch = True Then
            MsgBox "Cannot find " & pstrLastName, vbInformation, "BLAH"
        End If
    End If
End Sub

Private Sub mnuFindNavigateFirst_Click()
    mrstClient.MoveFirst
    mnuFindNavigateFirst.Enabled = False
    mnuFindNavigateLast.Enabled = True
    mnuFindNavigatePrevious.Enabled = False
    mnuFindNavigateNext.Enabled = True
End Sub

Private Sub mnuFindNavigateLast_Click()
    mrstClient.MoveLast
    mnuFindNavigateFirst.Enabled = True
    mnuFindNavigateLast.Enabled = False
    mnuFindNavigatePrevious.Enabled = True
    mnuFindNavigateNext.Enabled = False
End Sub

Private Sub mnuFindNavigateNext_Click()
    mrstClient.MoveNext
    If mrstClient.BOF Then
        mrstClient.MoveLast
        mnuFindNavigateFirst.Enabled = True
        mnuFindNavigateLast.Enabled = False
        mnuFindNavigatePrevious.Enabled = True
        mnuFindNavigateNext.Enabled = False
    Else
        mnuFindNavigateFirst.Enabled = True
        mnuFindNavigateLast.Enabled = True
        mnuFindNavigatePrevious.Enabled = True
        mnuFindNavigateNext.Enabled = True
    End If
End Sub

Private Sub mnuFindNavigatePrevious_Click()
    mrstClient.MovePrevious
    If mrstClient.BOF Then
        mrstClient.MoveFirst
        mnuFindNavigateFirst.Enabled = False
        mnuFindNavigateLast.Enabled = True
        mnuFindNavigatePrevious.Enabled = False
        mnuFindNavigateNext.Enabled = True
    Else
        mnuFindNavigateFirst.Enabled = True
        mnuFindNavigateLast.Enabled = True
        mnuFindNavigatePrevious.Enabled = True
        mnuFindNavigateNext.Enabled = True
    End If
End Sub
0
 
Anthony PerkinsCommented:
So you are using DAO and (Ugh!) DataControls.

1. How are you defining the DataControl?  In other words, what properties are set?  Connect, DefaultCursorType, DefaultType, Options, RecordsetType, Recordsource, etc.
2. What VB Service Pack do you have installed?
3. What version of Access are you using?

Anthony
0
 
goldenpawsAuthor Commented:
I take it that you don't care for DataControls, is there some other way to connect to a database?

1. DataControl: Connect = Access
                DefaultCursorType = 0 - DefaultCursor
                Default Type = 2 - UseJet
                Options = 0
                RecordsetType = 1 - Dynaset
                Recordsource = ClientServiceQry

2. VB sercive pack 5
3. Access 97
0
 
goldenpawsAuthor Commented:

Some History:  I wrote a client tracking application using Access. And now I'm trying to write a VB application to access the AccessDB and do all the same client tracking stuff.

ClientServiceQry is an Access query which is made up of two tables. Clients and ServiceDetail. Clients has fields like ClientID,FirstName, LastName, Street, etc. ServiceDetail has fields like ClientID, ServiceID, ServiceDate, ServiceType, TreatmentID. The fields I am using from these two tables are
ClientID, ServiceDate, ServiceType(from ServiceDetail)and
FirstName, LastName, Street, etc. (from Clients). Client is a one to many relationship with ServiceDetail using ClientID.  

What seems to be happening is the ClientID, FirstName, and LastName in the Clients table all get updated, but the ClientID in the ServiceDetail doesn't get updated. This part works fine in the Access application version.

I wonder why only one table (Clients) is being updated. What do you think?

0
 
goldenpawsAuthor Commented:
I just added a field (service type) to the VB form and both table (Clients and ServiceDetail) got updated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.