Solved

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

Posted on 2002-07-09
21
146 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:goldenpaws
  • 9
  • 6
  • 5
  • +1
21 Comments
 
LVL 3

Expert Comment

by:schworak
ID: 7142098
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
 

Author Comment

by:goldenpaws
ID: 7142129
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7142148
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
 
LVL 3

Expert Comment

by:schworak
ID: 7142419
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
 

Author Comment

by:goldenpaws
ID: 7142803
I have emailed you two zip files with the VB program and database files
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7143049
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7143565
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
 
LVL 3

Expert Comment

by:schworak
ID: 7144017
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
 
LVL 3

Expert Comment

by:schworak
ID: 7144062
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
 

Author Comment

by:goldenpaws
ID: 7144120
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:schworak
ID: 7144731
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
 

Author Comment

by:goldenpaws
ID: 7144802
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
 
LVL 3

Expert Comment

by:schworak
ID: 7144818
I am not sure what could have happened so I am sending you the updated version. to your email address.
0
 

Author Comment

by:goldenpaws
ID: 7146050
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7146310
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
 

Author Comment

by:goldenpaws
ID: 7146642
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7146726
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
 

Author Comment

by:goldenpaws
ID: 7146796
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 7146918
>>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
 

Author Comment

by:goldenpaws
ID: 7148303

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
 

Author Comment

by:goldenpaws
ID: 7148323
I just added a field (service type) to the VB form and both table (Clients and ServiceDetail) got updated.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now