Insert Statement doesn't put data into table

[FNA access]

I have a form that demands an input mask for a text box. The text box has a mask of three numbers then a space a dash another space and three more numbers.
This mask repeats itself upto a max of 4 times. ie
Auto id = 5 (679 - 574 - 678 - 571)

This data is stored in a table like:

AlertBaseTable
Auto Id Tankers
5 679574678571

What I want to do is store that data into a table that separates out the data into groups of three all related to the records auto id.

AlertBaseTanker Table
Auto Id Tankers
5 679
5 574
5 678
5 571

I then need to pull the data out of the tables I just inserted it into and concatenate them with each other placing a dash in between them for a report.

CombinedValuesT able
Auto Id Tankers
5 679 - 574 - 678 - 571


The code I have in place traces through perfect, but when it comes time for my SQL statement to fire it reads it and does nothing.

I will start with posting just my SQL statement as my code is quite large.

'Insert's Record into AlertBaseTanker
sSQL = "INSERT INTO AlertBaseTanker (AlertBaseID, AirTankerID) "
sSQL = sSQL & "VALUES ('" & strColumn1 & "', '" & Tanker & "')"
DoCmd.RunSQL (sSQL), dbonfailerror


One other question... When I execute an insert statement in VBA does it enter the data into the table immediately after leaving the execute statement.
What I need to now is if I can then query the table that I inserted values into later in the same sub.

The data integrity of my entire database relies on this bit of code and I have been struggling with it for weeks

Any help or insight will be greatly appreciated.

[mattkorguk]

You can call me simple if you like, but I would have been inclined to split the text box into 3s on screen! :)
I realise that doesn't help your situation and for that I applogise.

[FNA access]

That is what I did to begin with. I had 4 textboxes named tanker1 - tanker4.
However, this was difficult to manage when trying to pull data out of the tables.

I really need this code to work and I don't understand why it doesn't. It follows the exact paths that I need / want. I just don't have the insert statement putting data into my table. I have the same insert statement elsewhere in my code and it executes perfectly. I did a cut and paste, changed the table name and field names, but it doesn't work ???????

I am coding in the afterupdate event of my subform which is a datasheet. Each record represents a base that has multiple columns of information. Each record contains an autonumber BaseID as the base name can be used as many as three times in the same Day.

I can post my code for the event, but it is a bit on the long side. If someone wants to help me make it even more efficient, or more importantly get it to work how I need it to, I would be very greatfull.

Thanks in advance...

[FNA access]

I have trimed a bunch of excess code out that I don't think I need. I am now getting an error on the insert statement that I talked about above. I am getting a runtime error 3624 now. It states Could not read record; currently locked by another user.

As the code is much shorter I am going to post it. Please Help.

Public Function SeparateAfterupdate() As Boolean


Stop
'Variable declerations
Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String, dSQL As String, uSQL As String
Dim strColumn1 As String, strColumn2 As String, Tanker As String, TankerVar As String
Dim Start As Integer, Finish As Integer, iSQL As String
Dim vrst As DAO.Recordset, vSQL As String, TypeVar As String
Dim strColumnV As String

Set db = CurrentDb()



'Delete all records from tables Where AlertID is same as current record to enforce integrity
dSQL = "DELETE FROM AlertBaseTanker WHERE AlertBaseID = " & Me!AlertBaseID & "; "
DoCmd.RunSQL (dSQL)

dSQL = "DELETE FROM CombinedValuesAirTanker Where AlertBaseID = " & Me!AlertBaseID & "; "
DoCmd.RunSQL (dSQL)
  
    TankerVar = ""
    
    'Stores AlertBaseID into variable and tests the value stored in Airtankers for that record
    'If AirTankers is blank then inserts into CombinedValuesAirTanker Table for reports
    'If Airtankers has data then Seperates and stores string into AlertBaseTanker
    strColumn1 = Me!AlertBaseID
    If IsNull(Me!AirTankers) Or Me!AirTankers = "" Then    
            Tanker = ""
            iSQL = "INSERT INTO CombinedValuesAirTanker (AlertBaseID, AirtankerID)  " _
            & "VALUES('" & strColumn1 & "','" & Tanker & "');"
            DoCmd.RunSQL (iSQL)
   
    Else               'Stores AirTankers data into variable and sets the start position and length of string to be returned
        strColumn2 = Me!AirTankers
        Start = 1
        Finish = 3
    
        'Loops through string and breaks into groups of three and stores into table
        'all with the same corresponding AlertBaseID
        Do Until Start > Len(strColumn2) - 2  
            Tanker = Mid(strColumn2, Start, Finish)
            
            'Queries whe Tanker to see if one exists that is valid
            vSQL = "SELECT AirTankerID, Type, IsValid FROM Lookup_Airtanker " _
            & "WHERE AirTankerID = '" & Tanker & "' And IsValid = Yes;"
            Set vrst = db.OpenRecordset(vSQL, dbOpenSnapshot)
            
            'Test's to see if is valid Airtanker
            If Not vrst.BOF And Not vrst.EOF Then 
                vrst.MoveFirst
                TypeVar = vrst!Type
                Set vrst = Nothing
                
                    '*******This doesn't work**********
                
                'Insert's Record into AlertBaseTanker
                iSQL = "INSERT INTO AlertBaseTanker (AlertBaseID, AirTankerID)  " _
                & "VALUES('" & strColumn1 & "','" & Tanker & "');"
                DoCmd.RunSQL (iSQL)
                
                    '*************************************
                
                If TankerVar <> "" Then                  
                  'Can only have two types of planes at any one base for report
                   If Len(TankerVar) < 10 Then                          
                      'Test to see if value is already contained
                      If InStr(TankerVar, TypeVar) = 0 Then 
                          TankerVar = TankerVar & "/" & TypeVar
                      End If                    
                  Else                        
                       'Lets user know of error and goes to next record
                       MsgBox "You have more than two types of different Aircrafts" _
                       & " listed at the same base", vbOKOnly + 
                       vbInformation, "Aircraft " & _
                       "Input Error"
                       
                   End If                     
                'If TankerVar is nothing then insert type
                Else 
                   TankerVar = TypeVar
                End If                
            Else 
                'Informs user of input error
                MsgBox "         Airtanker " & Tanker & " is either not valid or not active" & vbCrLf _
                & " Please make the correction on the Alert form or Activate Airtanker", vbOKOnly + vbInformation _
                , "Aircraft Input Error"
                
            End If 
        'Moves search to next 3 digits
        Start = Start + 3
        Loop
        sSQL = "SELECT AlertBaseID, AirTankerID FROM AlertBaseTanker WHERE AlertBaseID = "
        sSQL = sSQL & Me!AlertBaseID & " ORDER BY AlertBaseID, AirTankerID ASC;"
        Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

        If Not rst.BOF And Not rst.EOF Then

            rst.MoveFirst
            strColumn1 = rst!AlertBaseID
            strColumn2 = rst!AirtankerID
  
            rst.MoveNext
            Do Until rst.EOF
                If strColumn1 = rst!AlertBaseID Then
                    strColumn2 = strColumn2 & " - " & rst!AirtankerID
                End If
                rst.MoveNext
            Loop
  
  
        End If
        sSQL = "INSERT INTO CombinedValuesAirTanker (AlertBaseID, AirtankerID)  " _
           & "VALUES('" & strColumn1 & "','" & strColumn2 & "');"
        DoCmd.RunSQL (sSQL)
      
        Set rst = Nothing
        
    End If
    
    If TankerVar <> "" Then          
        'Autofills type of aircraft in AlertBase
        uSQL = "UPDATE AlertBase SET AlertBase.Type = '" & TankerVar & "'" _
                & " Where AlertBase.AlertBaseID = " & rst!AlertBaseID & ";"
                DoCmd.RunSQL (uSQL)
    End If
    
    Set db = Nothing


End Function

[MMcCarthy]

OK you have two options. As said by a previous poster you can store four separate values.

Another option is to store the value with the separating dashes. Try using this as an input mask

000\-000\-000\-000;0;_

[mattkorguk]

Hi,
Just to check that it does work for this section (null)

'Stores AlertBaseID into variable and tests the value stored in Airtankers for that record
'If AirTankers is blank then inserts into CombinedValuesA irTanker Table for reports
'If Airtankers has data then Seperates and stores string into AlertBaseTanker
strColumn1 = Me!AlertBaseID
If IsNull(Me!AirTa nkers) Or Me!AirTankers = "" Then
Tanker = ""
iSQL = "INSERT INTO CombinedValuesA irTanker (AlertBaseID, AirtankerID) " _
& "VALUES('" & strColumn1 & "','" & Tanker & "');"
DoCmd.RunSQL (iSQL)


The fact that you're getting an error is at least something, it's not just that one of the fields has the focus still, I don't like to point out the obvious, but sometimes they can be missed?!
I'll keep looking...

[FNA access]

Thank you all for your input. I ended up putting in a button click to submit the form and ran the code there. The different inputmask would have eliminated my need for the combinedvalues table, however that part of the code was working good.

(I didn't know how to do that previous and after working with masks, I'm not a huge fan of them.) ; )

Curious though you both state an easier way would have been 4 seperate inputs. (As mentioned, that is what I originally set out to do) but I could only create an indeterminate relationship between those four fields to my validating table

Airtanker1, Airtanker2, Airtanker3, Airtanker4 to my table that holds valid Airtanker numbers.

Just a brief explaination would be great as on this project I have gone too far away from that to go back. I am a student and trying to learn all this on my own, as i haven't taken a database course yet.

Thanks again for all your help.

[MMcCarthy]

Thank you all for your input. I ended up putting in a button click to submit the form and ran the code there. The different inputmask would have eliminated my need for the combinedvalues table, however that part of the code was working good.

(I didn't know how to do that previous and after working with masks, I'm not a huge fan of them.) ; )

You're not alone. However, they can very occasionally be useful.

Curious though you both state an easier way would have been 4 seperate inputs. (As mentioned, that is what I originally set out to do) but I could only create an indeterminate relationship between those four fields to my validating table

Airtanker1, Airtanker2, Airtanker3, Airtanker4 to my table that holds valid Airtanker numbers.

Just a brief explaination would be great as on this project I have gone too far away from that to go back. I am a student and trying to learn all this on my own, as i haven't taken a database course yet.

Thanks again for all your help.

When you get a minute check out this tutorial

Data Normalisation and Table Structures.

Feel free to ask any questions.