Populating field with unbound text box 1

[MattLongmuir]

Hi Team,

Sadly, i'm still going to be a complete newbie for another few weeks - so try not to laugh at my insanely easy questions! (haha!)

Ok, here's the deal (basically):

tblTrainee
- FirstName (text field)
- LastName (text field)

frmAddTrainee
- txtfname (unbound text box)
- txtlname (unbound text box)
- cmdAdd (command button)

Simply enough, i want to be open the form, type in a users first and last names into those 2 text boxes, hit save and have the txt boxes populate their appropriate fields in the tblTrainee. Simple..or so i thought.

Here's an example of my code:

"Private Sub cmdAdd_Click()

Dim sSql As String

sSql = " INSERT INTO tblTrainee ( FirstName ) SELECT '" & Me("txtfname&quot & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True

End Sub"

Seems to work - i don't get any errors. However, the table doesn't populate!?
Can anyone help (either by supplying totally different code or by modifying my own)??

Also, as a side note, is there a way to remove the record er, toolbar from the bottom of a form? I think i read somewhere that you can't - if so that's ok. Would be cool if you could though.

Thanks in advance!

 

[MakeItSo]

No laugh for this..
I see your problem and it lies in the SQL statement.
The SELECT in an INSERT statement just limits the insertion, but doesn't insert. You need the VALUES:

INSERT INTO tblTrainee ( FirstName ) VALUES ('" & Me("txtfname&quot & &quot'"
;-)
Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MattLongmuir]

MakeItSo:

Thanks for the reply mate!
Make sense what you said, however, i've run into a nothing problem.

I made the changes you wrote, but not i get this error:

Run-time error '3134':
Syntax error in INSERT INTO statement.

Highlighted code is:
"DoCmd.RunSQL sSql"

Here's my code currently:

"Private Sub cmdAdd_Click()

Dim sSql As String


sSql = " INSERT INTO tblTrainee ( FirstName ) VALUES ('" & Me("txtfname&quot & &quot'"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True

End Sub"

Any ideas?

 

[MakeItSo]

D'oh, shhhh..!
I skipped one, too
VALUES ('" & Me("txtfname&quot & &quot'"
You are referring to your texbox-name in quotes - that won't work.
Change it to this:
sSql = " INSERT INTO tblTrainee ( FirstName ) VALUES ('" & Me!txtfname & &quot'"

Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MakeItSo]

And another:
move the last single quote inside the brackets:
" & Me!txtfname & "')"

should start testing before I paste code...


makeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MattLongmuir]

Makeitso:

You wouldn't believe it, but i'm getting the same error again..and wowzers, you've already answered it...uh oh. No go. I don't get any errors, but nothing is added to the table.

Here's my code atm. You're obviously whipping up a quick test using the info i've provided, perhaps you could paste ur code here?

"Private Sub cmdAdd_Click()

Dim sSql As String

sSql = " INSERT INTO tblTrainee ( FirstName ) VALUES ('" & Me!txtfname & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.SetWarnings True

End Sub"

 

[MattLongmuir]

If it helps, i'm running MS Access 2002 and i have not changed any of my libraries (i.e. i've havnet included the DAO libraries or anything...not sure if this would affect my results).

 

[MakeItSo]

Works fine on my machine now.
How have you checked if anything is added? Have you just opened the table left it open and clicked the button on the form after inserting a Name?

if yes: You must refresh...
Close your table, try to add something and then open the table and check if it has been inserted..

If no...
Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MattLongmuir]

MakeitSo:

Yes, i have the table closed, then i open the form, enter a name, click the add button. Then i close the form and open the table - nothing is there.

If it works on your machine, would you be able to send me your demo? My email is: mlongmuir@hotmail.com

It would be greatly appreciated!

 

[MakeItSo]

Underway...

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MattLongmuir]

MakeItSo:

Thanks for the file. On loading it, it came up with a message saying the VBA project in the db as corrupt. Suffice to say, all the code was gone. lol - will my troubles never end?

However, i know what the problem is now. I believe the problem to be my autonumber field (TraineeID) in the tblTrainee - i probably should have mentioned this before, sorry! Didn't think it would have this effect!

Once i removed it, the code worked. Sadly, i kinda need that TraineeID.

Any ideas?

Regardless of further replies, you deserve a big fat star for you efforts. Thanks dude! =)

 

[MakeItSo]

Ahh sure - I could have anticipated that.
Plus: Your error opening the db is the good old "Imported a Form with Code - shouldn't do that", which I forgot about...

Replace your sSql with the following:
This is tested and works with TranieeID:

sSql = " INSERT INTO tblTrainee ( TraineeID, FirstName ) VALUES (DMax("[TraineeID],"tblTrainee&quot+1, '" & Me!txtfname & "')"


MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MattLongmuir]

lol, you won't believe this. Before i read your post, i put the TraineeID field back in, and it now works!

What the?!

Anyway, i put your code in, just to be on the safe side. I got this compile error:
Expected: end of statement

[TraineeID] is highlighted

I tihnk there might be missing " somewhere..but not sure.

 

[MakeItSo]

Holy sh... I pasted the wrong line....
Just replace the double quotes within the DMax function by single quotes. I had changed this but pasted the wrong line...
sSql = " INSERT INTO tblTrainee ( TraineeID, FirstName ) VALUES (DMax('[TraineeID]','tblTrainee')+1, '" & Me!txtfname & "')"


Andy


Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MattLongmuir]

MakeItSo:

Thanks mate! Before i read your post i figured it out myself, by writing it like so:

"...DMax('" & [TraineeID] & "', '" &tblTrainee & "')+1, '" & Me!txtfname & "')"

Little bit more long-winded, but it gets the job done.

Anyway, i went on to add a few more fields and all is working perfectly now.

Thanks alot for your time and efforts - muchly appreciated.
Have another star, on the house.