How to copy ms access forms field data into ms access table

[zubeditufail]

Hello guys,
I am new to ms access development. I have a table and a form. I need to input data in the form and click on a button to add this data into the existing table.
This is part of a bigger application which will be compiled and given away for use.
tblProduct
id = autonumber
name = text
InHand = integer
Price = float

frmNew
txtname = textbox
txtInhand = textbox
txtPrice = textbox.

I need to coy frmNew.txtname into tblProduct.name and so forth.

Please help.
Best Regards
Tufail Ali

 

[JHB]

Normally you've the table or a query bound to the form's recordsource, then the data is automatic stored in your table.

 

[zubeditufail]

Thanks dear.
My purpose in MS access is to develop / designing / code a warehouse inventory + Point of Sale software in MS access.
Do you think it is a good idea to be working with Table directly by using a source record property?
I have always been of the opinion that we take input in field variable on a form, validate data and when found ok, append this data onto the table as a new record.
Is there any simple way to do that?

I tried docmd.runsql "Insert into table values..." but it does not seem to work?
any other way to accomplish this feat?
Thanks in advance.

 

[Minty]

I think you may be running before being able to walk a bit here.

Your theory is good, but as a starting project unbound forms are not the simplest to work with. You can use a SQL INSERT but there are different ways to achieve this. A quick search on here will help with that if you want to go that route.

I would start with bound forms while you develop your skills and your requirements, then migrate onto unbound validated forms. Adding fields and methods to unbound forms is a lot more time consuming whilst you are finding your feet.

Inventory is notoriously difficult to work with, if you search these forums you will see literally dozens of people struggling to get their heads around even simple stock control.

 

[zubeditufail]

Running would be an understatement, i would need to sprint.
the final deployment is envisaged to be installed on a server and two front end terminals

But yes, the route you say is logical. I have done some bound form already, displaying data of table, going through records. I stumbled upon an old VB6 book, introduced myself with DAO / ADO. Seriously thought that docmd.runsql (append into tbl (name, description .. ) values (frmname, frmdescription..) would do the trick but failed at it and hence my reason to seek advice from experts like you all!!
Any advice for fast track?

 

[JHB]

..
I have always been of the opinion that we take input in field variable on a form, validate data and when found ok, append this data onto the table as a new record.
Is there any simple way to do that?

Yeah - control it just after the data is input!
In the AfterUpDate event for the control could be one place to do it.

..
I tried docmd.runsql "Insert into table values..." but it does not seem to work?
any other way to accomplish this feat?
Thanks in advance.

What problem do you've have using the Insert Into statement?
Show your Append query!
Why are you not using Currency for you Price field?

Currency

The currency type is a special kind of decimal, with up to 4 digits on the right of the decimal point and up to 15 on the left. It was introduced for financial data and is available in all versions of Access. This type uses 8 bytes of disk space.
​

Float and Double

Floating point numbers are similar to numbers written in scientific notation, and come in two varieties: float and double. They have a precision of up about 7 decimal digits (float) or 15 digits (double). The exponent (location of decimal point) is stored separately, an can range from -45 to +38 (float) or from -324 to 308 (double). Use floating point numbers if you require a large range but only limited precision. Floats use 4 bytes, and doubles use 8 bytes of disk space.
​

 

[zubeditufail]

Here is my form with fields (named in red) and table in design view

the add button has the following code

Private Sub cmdAdd_Click()
Dim sqlstr As String

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO TblDir (fn, ln, gender) VALUES (cbfn.Value , cbln.Value, lstgender.Value );"

DoCmd.SetWarnings True

[tblDir subform].Requery


End Sub

Any suggestions for me? Thanks in advance!

 

[JHB]

If you insert text like you do it from a control:
First you must have the controlname outside the string.
Second it need to be surrounded by ' and drop the Value part, so try the below:

DoCmd.RunSQL "INSERT INTO TblDir (fn, ln, gender) VALUES ('" & cbfn & "', '" & cbln & "', '" & lstgender & "');"

 

[zubeditufail]

Thank you very very much!
The add Button is adding the values into the table.
Am I wrong in saying that this is an easy way to add / modify / query / delete records as compared to DAO / ADO?
Is my approach correct?

 

[JHB]

Thank you very very much!
The add Button is adding the values into the table.
Am I wrong in saying that this is an easy way to add / modify / query / delete records as compared to DAO / ADO?
Is my approach correct?

You're welcome - Happy New Year.
In such cases like yours - it is, but ... DAO/ADO is in other cases the right way to go!