Insert Into table issue

[musclecarlover07]

I have a button when pushed certain data is put into another table. I managed to figure it out for the most part. I ran into the proble if the text box has () or '. I need this for the Ability.Value field.

CurrentDb.Execute "INSERT INTO ship1(Pilot, Skill, Ability, Ship) Values('" & Pilot.Value & "', '" & PilotSkill.Value & "', '" & Ability.Value & "', '" & Ship.Value & "')"

This is what I tried. Idk if im forgetting another part. Look at the Ability.value

CurrentDb.Execute "INSERT INTO ship1(Pilot, Skill, Ability, Ship) Values('" & Pilot.Value & "', '" & PilotSkill.Value & "', chr(39)" & Ability.Value & "chr(39), '" & Ship.Value & "')"

I only need this for Ability and the pilot.

 

[SOS]

See if this helps:

CurrentDb.Execute "INSERT INTO ship1(Pilot, Skill, Ability, Ship) Values(" & Chr(34) & Pilot.Value & Chr(34) & "," & Chr(34) & PilotSkill.Value & Chr(34) & ", " & Chr(34) & Ability.Value & Chr(34) & ", " & Chr(34) & Ship.Value & Chr(34) & ")"

 

[musclecarlover07]

Dim strSQL As String
strSQL = "INSERT INTO ship1(Pilot, Skill, Ability, Ship) Values('" & Me.Pilot & "', '" & Me.PilotSkill & "', " & chr(39) & Me.Ability & chr(39) & ", '" & Me.Ship & "')"
CurrentDb.Execute strSQL

Gave me error:
Run-time error '3075'
Syntax error (missing operator) in query expression '"When attacking, reduce thye defenders agility value by 1 (to a mininimum of "0".','X-Wing')'

CurrentDb.Execute "INSERT INTO ship1(Pilot, Skill, Ability, Ship) Values(" & Chr(34) & Pilot.Value & Chr(34) & "," & Chr(34) & PilotSkill.Value & Chr(34) & ", " & Chr(34) & Ability.Value & Chr(34) & ", " & Chr(34) & Ship.Value & Chr(34) & ")"

Gave me error:
Run-time error '3075'
Syntax error (missing operator) in query expression '"When attacking, reduce thye defenders agility value by 1 (to a mininimum of "0".".

Idk if this helps any but the text in the box i need to copy over to the new table:
When attacking, reduce the defender's agility value by 1 (to a minimum of "0").

 

[musclecarlover07]

I'm sorry but Im not sure what your suggestion means. I have very little knowledge in access. Most of the time I find what I need online then I modify that to my needs. Some of the code I am able to decypher other times I just go with it.

 

[musclecarlover07]

everything else works but that one statement.

 

[musclecarlover07]

Print strSQL = "INSERT INTO ship1(Pilot, Skill, Ability, Ship) Values('" & Me.Pilot & "', '" & Me.PilotSkill & "', " & Chr(39) & Me.Ability & Chr(39) & ", '" & Me.Ship & "')"
CurrentDb.Execute strSQL

I dont think this is right. I am sorry but Im lost on exactly how to do this.

 

[musclecarlover07]

I have no clue how to do this. I tried typing it in the Immediate window but that does not help. I do not know where to find the DEBUG window. I apoligize if Im making this more difficult then it is.

 

[JHB]

When I take you SQL-string and execute it, the result is going into the table as expected.

I've attached a small database - then you can compare the table, field names control names etc. with yours.

 

[musclecarlover07]

The only problem with what you provided is that there is an ' in the word defender's
This is how is should read:
When attacking, reduce the defender's agility value by 1 (to a minimum of "0").
If i remove the ' out of defenders it works. But I want the ' in defenders because that is how it is spelled.

 

[JHB]

This is how is should read:
When attacking, reduce the defender's agility value by 1 (to a minimum of "0").
If i remove the ' out of defenders it works. But I want the ' in defenders because that is how it is spelled.

But it wasn't so you have wrote it:

Gave me error:
Run-time error '3075'
Syntax error (missing operator) in query expression '"When attacking, reduce thye defenders agility value by 1 (to a mininimum of "0".".

It should work now, with and without '.

 

[musclecarlover07]

Ah. You make a good point. You can only use what info I put. I mistyped it. My apoligies on that. But that worked beautifully. Thank you so much. Now I can continue and fix other issues.