unbound form to record data

[pronate]

Hi,

i am trying to have a user to enter some data into an unbound form and then upon clicking a "ok" button, i want to run a VB code to check the validitiy of the data, if ok, add it into as a new record in the data table.

I know a bit of VB coding in Access, but am stuck with the "add it as a new record", any pointer? How do i assign the different textbox values to the fields?

Cheers
Sam

 

[KenReay]

Hi

Several ways to do this but minimal code method has to be to build an SQL string, and execute it.

I cannot give you exact code becuase I do not know the names of you table and controls but the basic idea is

Dim strSQL as String

strSQL ="INSERT INTO MyTable (Col1, Col2) VALUES ('" & txtBox1 & "','" & txtBox2 & "');"

doCmd.RunSQL

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pronate]

Thank!, why haven't i thought of that? I use append query before....

I was actually thinking along recordset.....by the way i don't see a lot of book talking about unbound forms.

 

[DonQuichote]

We do everything unbound in Access. And we normally use recordsets instead of queries where possible. It is great from a performance point of view and it also enables us to use object-oriented program designs.

Most books don't talk about that. Most books are written to easily enable you to build a database. You will have to look in the "how about performance" chapters for recordsets and such things.

Best regards

 

[sxschech]

I have a more global "unbound" question. If I want to display all records on a continuous form (to look like a datasheet view) and only have a single update upon closing the form ("Save all changes, Yes or No&quot for all changes regardless of what record row had been edited should this be done with a temp table and an update query or is there another approach as above using a recordset?

 

[KenReay]

Hi

By using a temp table, you are using a recordset, a table is simply a special form of recordset

In a continuous (bound) form updates take place as you move from row to row, there is no means to undo them, so I cannot see any other way to do what you propose other than a temporary table (if your Db is multi user you will have to pull a few tricks to give each user unique access to this temporary table, or have the temporary table as a local table in the front end) as you say your command buttons then eitehr dump the changes, or execute an update query which updates the 'main' table with the content of the temporary table

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pronate]

Thanks for all your responses.

I tried the append query SQL, it works fine, still, i am interested to learn about malipulating recordsets.

Has anyone got a articles in the web that talks about this?

 

[KenReay]

Hi

If you want to learn about just about all things Access and a bit more besides, invest in Access Developers Handbook, it is not cheap, but it is worth it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[NiallWestland1974]

Hi,
I'm doing something along the same lines I guess. One form, that feeds into two separate tables.

The problem is that the autonumber of the new record from one of the tables is needed as a foreign key in the second table. But when you create a new record in the first table, the autonumber isn't generated until an entry has been made into one of that records fields - without submitting the content of the controls for one form separately from the controls of another (thereby generating the autonumber, and allowing it to be passed into the second part of the form), how am I going to find out the autonumber? A disconnected recordset?