UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Statement... With Fields... Argh, Access 2016    
 
   
BigPapaPSP
post Feb 14 2018, 08:16 PM
Post#1



Posts: 23
Joined: 14-February 18



I have a database I'm using to generate scoring for a 4-person table game. Each round is made up of 4 hands. Each hand requires an entry in the "tbl_Rounds" table. I have approximately 8 fields that I need to insert into the table for each hand.

I can easily get it to pop test records into the table using:



Dim strSQL As String

' WRITE RECORDS TO ROUNDS DETAILS
' [RoundName], [RoundType},[RoundPlayerCt}, [Archived}
strSQL = "INSERT INTO tbl_RoundDetails ( [RoundType], [RoundPlayerCt], [Archived]) VALUES (1,5,0); "

MsgBox ("--> " & strSQL & "<---DATA ")
CurrentDb.Execute strSQL, dbFailOnError

However, when I try to put data in, I get a slew of errors.

' WRITE RECORDS TO ROUNDS TABLE
'strSQL = "INSERT INTO tbl_Rounds([EventID], [RoundID], [HandID], [HandStatus], [Wind1], [Wind2], [Wind3], [Wind4], [Wind5], [GameTypeID]) VALUES (" & [EventID] & ",1,1,1,1,1,1,1,1,1) "
'MsgBox ("--> " & strSQL & "<---DATA")
'CurrentDb.Execute strSQL, dbFailOnError

All of these fields are integers, and not strings.


The popup box that shows strSQL looks proper.

Any assistance would be appreciated
Go to the top of the page
 
DanielPineault
post Feb 14 2018, 08:26 PM
Post#2


UtterAccess VIP
Posts: 5,983
Joined: 30-June 11



Where is [EventID] coming from exactly? The form?

In that case, what about:

CODE
    strSQL = "INSERT INTO tbl_Rounds([EventID], [RoundID], [HandID], [HandStatus], [Wind1], [Wind2], [Wind3], [Wind4], [Wind5], [GameTypeID]) " & _
             "VALUES (" & Me.EventID & ",1,1,1,1,1,1,1,1,1) "
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError





Also, in the above, notice the Debug.Print of the strSQL that will printout the SQL statement in the immediate windows. Copy/Paste it into the Query editor and see what it complains about. This is the best way to troubleshoot this type of thing.



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
GroverParkGeorge
post Feb 14 2018, 08:37 PM
Post#3


UA Admin
Posts: 33,271
Joined: 20-June 02
From: Newcastle, WA


Hm.

What are the repeating fields? [Wind1], [Wind2], [Wind3], [Wind4], [Wind5]

That looks suspiciously like a spreadsheet, not a relational table in Access.

This is a design problem that needs to be addressed sooner rather than later.

--------------------
Go to the top of the page
 
BigPapaPSP
post Feb 14 2018, 08:55 PM
Post#4



Posts: 23
Joined: 14-February 18



[EventID] is a field in the current table of the form in a combo box. Currently there is several drop down combo boxes to select EventID, RoundID, etc... and those corresponding unique record id's are to be pushed into the new records being created.

EventID is a field in a couple of my tables. I've tried clarifying with [table].[field] or [table]![field] without success.

D
Go to the top of the page
 
BigPapaPSP
post Feb 14 2018, 09:00 PM
Post#5



Posts: 23
Joined: 14-February 18



That game is 4 player, and for each hand, there different positions. They corespond to the 4 winds... NORTH SOUTH EAST WEST. wind1 is the current wind for player 1, wind2 is current wind for player 2 ---

So, HAND 1 of 4

Wind1 = East Wind
wind2 = south wind
wind3 = west wind
wind4 = north wind

Hand 2 of 4

The game rotates

Wind1= North
wind2 = east
wind 3 = south
wind 4 = west

at the end of each of the four rounds, every "positon" has played as every wind...

It's a hong kong mahjong game if your curious...

Oh, and the winds correspond to a table of winds 1=east, 2=south, 3=west, 4=north, 5=rotator (the 5th wind doesn't need an explanation, but is used when a 5th player is included).
Go to the top of the page
 
GroverParkGeorge
post Feb 14 2018, 09:56 PM
Post#6


UA Admin
Posts: 33,271
Joined: 20-June 02
From: Newcastle, WA


As I feared, then. A spreadsheet style table leads to problems that you won't want to incur. Invest some time in reading about relational tables and see about correcting this.


--------------------
Go to the top of the page
 
BigPapaPSP
post Feb 14 2018, 10:32 PM
Post#7



Posts: 23
Joined: 14-February 18



Ya know, I have no idea what you did differently, but I got it to work. I had tried the me.[field], but I probably had another error or typo somewhere. Thanks for the help.

This is what I ended up with:

strSQL = "INSERT INTO tbl_Rounds([EventID], [TableID], [RoundID], [HandID], [HandStatus], [Wind1], [Wind2], [Wind3], [Wind4], [Wind5], [GameTypeID]) " & _
"VALUES (" & Me.EventID & "," & Me.TableID & "," & lastID & ",1,1,1,2,3,4,5," & Me.GameTypeID & ") "
' Debug.Print strSQL
' MsgBox ("<SQL>" & strSQL & "</SQL>")
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO tbl_Rounds([EventID], [TableID], [RoundID], [HandID], [HandStatus], [Wind1], [Wind2], [Wind3], [Wind4], [Wind5], [GameTypeID]) " & _
"VALUES (" & Me.EventID & "," & Me.TableID & "," & lastID & ",2,1,4,1,2,3,5," & Me.GameTypeID & ") "
' Debug.Print strSQL
' MsgBox ("<SQL>" & strSQL & "</SQL>")
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO tbl_Rounds([EventID], [TableID], [RoundID], [HandID], [HandStatus], [Wind1], [Wind2], [Wind3], [Wind4], [Wind5], [GameTypeID]) " & _
"VALUES (" & Me.EventID & "," & Me.TableID & "," & lastID & ",3,1,3,4,1,2,5," & Me.GameTypeID & ") "
' Debug.Print strSQL
' MsgBox ("<SQL>" & strSQL & "</SQL>")
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO tbl_Rounds([EventID], [TableID], [RoundID], [HandID], [HandStatus], [Wind1], [Wind2], [Wind3], [Wind4], [Wind5], [GameTypeID]) " & _
"VALUES (" & Me.EventID & "," & Me.TableID & "," & [lastID] & ",4,1,2,3,4,1,5," & Me.GameTypeID & ") "
' Debug.Print strSQL
' MsgBox ("<SQL>" & strSQL & "</SQL>")
CurrentDb.Execute strSQL, dbFailOnError

Thanks again... only thing left for this routine is I have to add a [EventDate] field, and pass it a date value... but I can manage that now that the insert is working.

D
This post has been edited by BigPapaPSP: Feb 14 2018, 10:33 PM
Go to the top of the page
 
BigPapaPSP
post Feb 14 2018, 11:17 PM
Post#8



Posts: 23
Joined: 14-February 18



QUOTE
As I feared, then. A spreadsheet style table leads to problems that you won't want to incur. Invest some time in reading about relational tables and see about correcting this.


I'm aware of how to use relational tables, and I use them extensively in the project. But for these 5 fields it makes no sense to take wind1 .. wind5 and translate that into 4 additional records just so I can avoid 3 field names in my table. there is a point where you can over complicate things here. And it certainly makes it much easier dealing with a single record per hand, than multiple records per hand per round, and making sure they relate to each other and calculate3d properly between records.

Thanks for the advise though.

D

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st August 2018 - 06:12 PM