473,327 Members | 2,016 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Insert Statement doesn't put data into table

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
7 5281
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
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
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
14,534 Expert Mod 8TB
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
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
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
14,534 Expert Mod 8TB
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

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

Similar topics

1
by: avinash | last post by:
hi my self avi i want to copy data from one table to other table,by giving certain condition and i want o use insert statement .in this i want to pass some value directly and some value from...
9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
5
by: Klemens | last post by:
I get SQL30090 reason 18 by trying to do an insert in a federated table and an update in a local table in one transaction Do I have to change some settings to get done or ist this not possible by...
6
by: Larry Johnson | last post by:
I have two similar SQL Server databases each with a table named Payments. PaymentID is an identity field and the primary, unique, key in both tables. There is one other key but it is not unique. ...
6
by: efgh | last post by:
I'm fairly confident in my knowledge of SQL but I'm stumped with regards with an Insert Into statement in Access 2003. I've got a 4 column table that I've simplified about as much as I can, no...
2
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request :...
6
by: rn5a | last post by:
During registration, users are supposed to enter the following details: First Name, Last Name, EMail, UserName, Password, Confirm Password, Address, City, State, Country, Zip & Phone Number. I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.