Create a record in a table from an unbound form 1

[NFI]

Hello,

right, before I bash my head against this any more, I just want to see if it's actually possible...I'm starting to think it's not - or that I should be doing it some completely different way...

Just for the sheer fun of it, I'm creating a database to create adventure game characters (why not?). They exist in a table called character which has the following fields;

*name  Text
race   Text
sex    Text
STR    Number
INT    Number
DEX    Number
WIZ    Number
CON    Number
HP     Number
AC     Number
EXP    Number

I thought it'd be a jolly good idea to put together a form to generate charatcters, so I created a form which isn't actually associated with any table (although I think this was a mistake and I don't know how to get out of it, now) but which has all the fields of the character table on it. Instead of pulling data out of the character table, though, the form generates random values, based on a set of modifiers in another table, called race.

*race     Text
modSTR    Number
modINT    Number
modDEX    Number
modWIZ    Number
modCON    Number
modHP     Number
modAC     Number

The way this works is there's a combo-box on the form which is bound to race.race and, when you select a particular race, each of the text boxes on the form run the source data procedure;

=(Int(Rnd()*15))+3+DLookUp("[modSTR]","race","race.race='" & cmbRace.Value & "'")

...modSTR in this case, but modINT for INT etc.

Anyway (assuming anybody's still reading this and hasn't given up in disgust at how geeky this is) having generated a character, I then want to add it to the character table, so I added a command button and tried the following in it's on click event procedure;

DoCmd.GoToRecord acDataTable, "character", acNewRec

...which, trying to get my head around the unintelligable microsoftese help files on the GoToRecord method/procedure, I thought made sense. But I get the error;

The object character is not open.

To be honest, I wasn't entirely surprised, as I couldn't find any mention of actually taking the data from the form and putting it in fields in the table. I was sort of hoping for some sort of function which allowed me to do an SQL statement along the lines of;

INSERT INTO "character"
name=[txtName].[value]
race=[txtRace].[value]
...

...and so on.

However, I thought I'd try and fix this object not open problem first, so, grasping at straws, I tried this;

Dim db   As DAO.Database
Dim rst  As DAO.Recordset

  Set db = Access.Application.CurrentDb
  Set rst = db.OpenRecordset("character")

    DoCmd.GoToRecord acDataTable, "character", acNewRec

...but I just get the same error

So...does any of this make sense? Am I doing it the wrong way? Can what I want to do actually be done? Should I just start again and bind the form to the character table? Should I just stop posting stupid questions and learn more Access? ...Actually, that's what I'm trying to do, so any patient help will be much appreciated.

Thanks and well done on getting to the end here,

Paul

 

[PHV]

Have a look at the DoCmd.RunSQL or CurrentDb.Execute methods.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NFI]

will do, thanks for that

Paul

 

[NFI]

I tried the DoCmd.RunSQL and it worked fine.

To be honest, I'm rather surprised at how easy that was to do!

Thanks again for your help,

Paul