2 related access tables connected to one vb form, problem with saving data

[AiVrea]

Hi, I have 2 access tables (table1, table2), table2 is related to the field "Nr Crt"(which is an AutoNumber) in table1 in a "one-to-many" relation. Table1 is connected to the vb form to textboxes, table2 is in DataGridView. Whenever I try to add data, the AutoNumber is set to -1, so the data from table2 is not saved correctly.I need a code for when I click the Add button the "Nr Crt" autonumber field will automatically be increased by 1.
The circled textbox in the image is the "Nr Crt" field in table1, and the circled field in the DataGridView is the related field from table2.The picture is taken right after I pressed the Add button.And I need a code that will automatically increase the "Nr Crt" field by 1 instead of setting it to -1.

[jimatqsi]

Perhaps you've heard this before, but keying or linking tables based on an auto-number field is a very, very bad idea. Those creatures can get corrupted and have to be reset, plus you have problems like the one you're grappling with now.

You're going to have to base this form on a query to one table only. When an add gets done, it will add only a record to that table. Then you'll have to add some code fired by an event handler to add the data to table 2 based on what just got added to table 1.

You would be much better off to draw a line in the sand right now to say "no more keys or joins on auto-number fields" and do the necessary coding to make a function to maintain a "next available number" parameter table. I'll post some code I've got that does that very thing.

Jim

[AiVrea]

Yeah I pretty much understood the problem and I was thinking to add 2 extra buttons of add and save for the secondary table. So when I click the add button on top it only connects to table1 and the save button on top also saves only in table1. And the 2 new buttons will add and save in table2. I'll leave the NrCrt(table1) textbox and the corresponding field in table2 visible so that after saving data into table1 the Nr Crt will be visible and can be entered in table2 in the DataGrid view.
Tell me if you think it's a good idea. and thx for the reply.

[jimatqsi]

No, it does not sound like a very good design plan. You want to make one form independently maintaining two unjoined tables. Requires too much attention on the part of the user and you're very likely to end up with unmatched records in each table. There's no good reason for it. It's not like you're up against a wall, with no other good strategies available.

Add a column to each table to represent a real key field. Run an update query that will copy the auto-generated number into the new key field in each table. Then change the form to generate keys for any future records added to table one, and change the underlying query to join based on this new key field rather than the auto-gen field.

Jim

[jimatqsi]

Here's the code for generating your own keys. It makes some assumptions about the names of tables and columns and key format, but you can alter that to suit your DB scheme.

Jim

Option Compare Database
Option Explicit
 
Public Function NextID(strTable, Optional strPrefix, Optional bVerify As Boolean) As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim lngNextID As Long
Dim rsTargetTbl As DAO.Recordset    '
Dim strSQL As String
 
' strTable is the table we want to form a new key for
' strPrefix is an optional prefix for the formulated key
' bVerify indicates whether to verify the key is unique or not
 
On Error GoTo Err_NextID
 
Set dbs = CodeDb
NextID = ""
Do Until NextID <> ""
    ' select only the row containing the ID for the selected table
    strSQL = "Select * from tblTableIDs where strTableName=""" & strTable & """"
    Set rs = dbs.OpenRecordset("tblTableIDs", dbOpenDynaset, dbSeeChanges)
        rs.Edit
            lngNextID = rs!NextID + 1
            rs!NextID = lngNextID
        rs.Update
 
        If Nz(strPrefix, "") = "" Then ' was a prefix supplied?
            NextID = Format(lngNextID, "000000") ' you might want a different format
        Else
            NextID = strPrefix & Format(lngNextID, "000000") ' add the prefix to the key
        End Function
    rs.Close
 
    If Nz(bVerify, False) = True Then
        strSQL = "select ID from " & strTable & " where ID=""" & NextID & """"
        Set rsTargetTbl = dbs.OpenRecordset(strSQL, dbReadOnly)
        If Not rstargetbl.EOF Then
            NextID = "" ' can't use this ID because it is already in the table
        End If
        rsTargetTbl.Close
    End If
Loop        ' until nextid is not ""
 
Exit_NextID:
    rs.Close
    rsTargetTbl.Close
    Set rs = Nothing
    Set rsTargetTbl = Nothing
    Set dbs = Nothing
Exit Function
 
Err_NextID:
    NextID = ""
    GoTo Exit_NextID
 
 

[AiVrea]

Thanks for the code, it's perfect for what I needed.