Take a field from a form and add to table

[ianbrown76]

Hi,

How do I take a value in a field in a form and place it into a field in a table? I'm wanting to put the 'products sold' from my form into a table called 'Products' and the field being 'Products sold'. I can't seem to name the appropriate fields correctly.

My coding is

Tables![produts]![products sold] = Me![products sold]

If anyone can help me I'd most appreciate it.

Ian

 

[PaulBricker]

Ian, is the Form bound to the Table. If it is, then you just need to set the Control Source for the Textbox [products sold] to the field [products sold] in the Table. If the Form is unbound, then you will need to write code in the after update event to move that value to the Table. Let's start with the first question and work from there.

Paul

 

[ianbrown76]

Hi Paul,

The form is not bound to anything, it just takes calculations from queries and I'd like to put those into a table to store.

I've added a button to my form which opens a table 'Products' and then goes to a new record. I was then hoping to take the information from the form and place into the relevant fields.

I just don't seem to be able to give the fields in the table the correct name.

Thanks in advance for the help.

 

[PaulBricker]

Well this is the general way to do that.

Private Sub cmbAddRecord_Click()
Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("products",dbopendynaset)
rst.AddNew
rst![products sold] = Me.[products sold]
rst![someotherfieldintable] = Me.[someotherfieldonform]
rst.Update

Set rst = Nothing

End Sub

Paul

 

[ianbrown76]

Hi,

Sorry for more hassle, I've typed in the following

Dim rstprod As Recordset
Set rstprod = CurrentDb.OpenRecordset("Product", dbopendynaset)
rstprod.AddNew

then the fields I'd like to populate

but end up with an error message saying

Run time error 3001,
Invalid argument,

Could I have typed something incorreclty?

 

[PaulBricker]

Ian, I'll need to see all the code to know what's up. Sounds like a syntax problem. Post it all and I'll see if I can sort it out.

Paul

 

[ianbrown76]

Hi,

This is my code, I hope you can help.

Private Sub addrecs_Click()

Dim DBS As Database, rstprod As Recordset
Set DBS = CurrentDb
Set rstprod = CurrentDb.openrecordset("product", dbOpenDynaset)

rstprod.AddNew

rstprod![Date] = "200209"
rstprod![Department] = "Tele"
rstprod![non warr items] = Me.[non warr]
rstprod![warr items] = Me.[warr items]
rstprod.Update

Set rstprod = Nothing

End Sub

Ian

 

[PaulBricker]

Where is the code stopping. Can you debug it and see exactly which line it's stopped on. The first thing that might be it, although I don't know why, would be
rstprod![Date] = "200209"
Depending on what the datatype for rstprod![date] is, you are trying to pass it a string value. But that should trigger a datatype mismatch error, not an argument error. See if you can find which line it bombs on and let me know.

Paul

 

[ianbrown76]

The Code stops with an 'invalid argument' at

Set rstprod = CurrentDb.openrecordset("product", dbOpenDynaset)

if I remove

Dim DBS As Database
Set DBS = CurrentDb

if I don't it highlites the 'DBS As Databse'.
The 'Date' is defined as a string in the table so that shouldn't be the problem.

Thanks for the help it's much appreciated.

Ian

 

[PaulBricker]

Private Sub addrecs_Click()

Dim rstprod As Recordset
Set rstprod = CurrentDb.OpenRecordset("product", dbOpenDynaset)

rstprod.AddNew

rstprod![Date] = "200209"
rstprod![Department] = "Tele"
rstprod![non warr items] = Me.[non warr]
rstprod![warr items] = Me.[warr items]
rstprod.Update

Set rstprod = Nothing

End Sub

Ian, try it like this. As long as we tell it to use the CurrentDb in our Set rstprod line, we don't need to declare a variable for it. What version of Access are you using. It's possible it's a reference problem.

Paul

 

[ianbrown76]

Hi,

Its Access 2000. The debugger still seems to stop at the same line.

Is there another way around it?

 

[PaulBricker]

Ian, let's check the references. Open the module with the code in it. On the menu bar go to Tools...References and look to see if the library
Microsoft DAO 3.6 Object Library has a check mark next to it. If it doesn't, put a checkmark in the box next to it and save the changes. Then change your code to this.

Dim rstprod As DAO.Recordset
Set rstprod = CurrentDb.OpenRecordset("product", dbOpenDynaset)

rstprod.AddNew

rstprod![Date] = "200209"
rstprod![Department] = "Tele"
rstprod![non warr items] = Me.[non warr]
rstprod![warr items] = Me.[warr items]
rstprod.Update

Set rstprod = Nothing

End Sub


Paul