By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Insert Statement doesn't put data into table

P: 36
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.

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

CombinedValuesTable
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.
Jul 16 '07 #1
Share this Question
Share on Google+
7 Replies


P: 28
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.
Jul 16 '07 #2

P: 36
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...
Jul 16 '07 #3

P: 36
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function SeparateAfterupdate() As Boolean
  2.  
  3.  
  4. Stop
  5. 'Variable declerations
  6. Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String, dSQL As String, uSQL As String
  7. Dim strColumn1 As String, strColumn2 As String, Tanker As String, TankerVar As String
  8. Dim Start As Integer, Finish As Integer, iSQL As String
  9. Dim vrst As DAO.Recordset, vSQL As String, TypeVar As String
  10. Dim strColumnV As String
  11.  
  12. Set db = CurrentDb()
  13.  
  14.  
  15.  
  16. 'Delete all records from tables Where AlertID is same as current record to enforce integrity
  17. dSQL = "DELETE FROM AlertBaseTanker WHERE AlertBaseID = " & Me!AlertBaseID & "; "
  18. DoCmd.RunSQL (dSQL)
  19.  
  20. dSQL = "DELETE FROM CombinedValuesAirTanker Where AlertBaseID = " & Me!AlertBaseID & "; "
  21. DoCmd.RunSQL (dSQL)
  22.  
  23.     TankerVar = ""
  24.  
  25.     'Stores AlertBaseID into variable and tests the value stored in Airtankers for that record
  26.     'If AirTankers is blank then inserts into CombinedValuesAirTanker Table for reports
  27.     'If Airtankers has data then Seperates and stores string into AlertBaseTanker
  28.     strColumn1 = Me!AlertBaseID
  29.     If IsNull(Me!AirTankers) Or Me!AirTankers = "" Then    
  30.             Tanker = ""
  31.             iSQL = "INSERT INTO CombinedValuesAirTanker (AlertBaseID, AirtankerID)  " _
  32.             & "VALUES('" & strColumn1 & "','" & Tanker & "');"
  33.             DoCmd.RunSQL (iSQL)
  34.  
  35.     Else               'Stores AirTankers data into variable and sets the start position and length of string to be returned
  36.         strColumn2 = Me!AirTankers
  37.         Start = 1
  38.         Finish = 3
  39.  
  40.         'Loops through string and breaks into groups of three and stores into table
  41.         'all with the same corresponding AlertBaseID
  42.         Do Until Start > Len(strColumn2) - 2  
  43.             Tanker = Mid(strColumn2, Start, Finish)
  44.  
  45.             'Queries whe Tanker to see if one exists that is valid
  46.             vSQL = "SELECT AirTankerID, Type, IsValid FROM Lookup_Airtanker " _
  47.             & "WHERE AirTankerID = '" & Tanker & "' And IsValid = Yes;"
  48.             Set vrst = db.OpenRecordset(vSQL, dbOpenSnapshot)
  49.  
  50.             'Test's to see if is valid Airtanker
  51.             If Not vrst.BOF And Not vrst.EOF Then 
  52.                 vrst.MoveFirst
  53.                 TypeVar = vrst!Type
  54.                 Set vrst = Nothing
  55.  
  56.                     '*******This doesn't work**********
  57.  
  58.                 'Insert's Record into AlertBaseTanker
  59.                 iSQL = "INSERT INTO AlertBaseTanker (AlertBaseID, AirTankerID)  " _
  60.                 & "VALUES('" & strColumn1 & "','" & Tanker & "');"
  61.                 DoCmd.RunSQL (iSQL)
  62.  
  63.                     '*************************************
  64.  
  65.                 If TankerVar <> "" Then                  
  66.                   'Can only have two types of planes at any one base for report
  67.                    If Len(TankerVar) < 10 Then                          
  68.                       'Test to see if value is already contained
  69.                       If InStr(TankerVar, TypeVar) = 0 Then 
  70.                           TankerVar = TankerVar & "/" & TypeVar
  71.                       End If                    
  72.                   Else                        
  73.                        'Lets user know of error and goes to next record
  74.                        MsgBox "You have more than two types of different Aircrafts" _
  75.                        & " listed at the same base", vbOKOnly + 
  76.                        vbInformation, "Aircraft " & _
  77.                        "Input Error"
  78.  
  79.                    End If                     
  80.                 'If TankerVar is nothing then insert type
  81.                 Else 
  82.                    TankerVar = TypeVar
  83.                 End If                
  84.             Else 
  85.                 'Informs user of input error
  86.                 MsgBox "         Airtanker " & Tanker & " is either not valid or not active" & vbCrLf _
  87.                 & " Please make the correction on the Alert form or Activate Airtanker", vbOKOnly + vbInformation _
  88.                 , "Aircraft Input Error"
  89.  
  90.             End If 
  91.         'Moves search to next 3 digits
  92.         Start = Start + 3
  93.         Loop
  94.         sSQL = "SELECT AlertBaseID, AirTankerID FROM AlertBaseTanker WHERE AlertBaseID = "
  95.         sSQL = sSQL & Me!AlertBaseID & " ORDER BY AlertBaseID, AirTankerID ASC;"
  96.         Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
  97.  
  98.         If Not rst.BOF And Not rst.EOF Then
  99.  
  100.             rst.MoveFirst
  101.             strColumn1 = rst!AlertBaseID
  102.             strColumn2 = rst!AirtankerID
  103.  
  104.             rst.MoveNext
  105.             Do Until rst.EOF
  106.                 If strColumn1 = rst!AlertBaseID Then
  107.                     strColumn2 = strColumn2 & " - " & rst!AirtankerID
  108.                 End If
  109.                 rst.MoveNext
  110.             Loop
  111.  
  112.  
  113.         End If
  114.         sSQL = "INSERT INTO CombinedValuesAirTanker (AlertBaseID, AirtankerID)  " _
  115.            & "VALUES('" & strColumn1 & "','" & strColumn2 & "');"
  116.         DoCmd.RunSQL (sSQL)
  117.  
  118.         Set rst = Nothing
  119.  
  120.     End If
  121.  
  122.     If TankerVar <> "" Then          
  123.         'Autofills type of aircraft in AlertBase
  124.         uSQL = "UPDATE AlertBase SET AlertBase.Type = '" & TankerVar & "'" _
  125.                 & " Where AlertBase.AlertBaseID = " & rst!AlertBaseID & ";"
  126.                 DoCmd.RunSQL (uSQL)
  127.     End If
  128.  
  129.     Set db = Nothing
  130.  
  131.  
  132. End Function
  133.  
Jul 17 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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;_
Jul 18 '07 #5

P: 28
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 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)


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...
Jul 18 '07 #6

P: 36
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.
Jul 24 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jul 24 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.