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.
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.
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...
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
-
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;_
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...
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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,...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
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 :...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |