How to enable vba code to read and retrieve data from table? (1 Viewer)

Hello guys, I would like to ask how do I enable my vba code to read and retrieve data from my table so that i will be able to calculate the grossweight by clicking onto "Calculate Grossweight" button on my form?

I tried using SQL method, and it prompt me error 3078 which say that "the Microsoft Access database engine cannot find the input table or query 'Calculate mxd SET LBS =100, Content = '100%Cotton', Reference = 'Bio', GrossWeight = ",Nettweight = '43.35'. Make sure it exists and that its name is spelled correctly. "

Below is my code:

CurrentDb.Execute "Calculate mxd" & _
" SET LBS = " & Me.txtLbs & _
", Content = '" & Me.txtContent & "'" & _
", Reference = '" & Me.txtReference & "'" & _
", GrossWeight = '" & Me.txtGrossweight & "'" & _
", NettWeight = '" & Me.txtNettweight & "'"

If Lbs <= 130 And Content = "100%Content" And Reference Like "Bio" Then
GrossWeight = (NettWeight * (102 / 100)) + 20
End If
I get that error msg on the code that i bold.

How do I fix that? Thank you.

You code is not even close to a executable query, or a select query, or a sensible bit of VBA!

Being a little blunt here - but why do you insist on trying to create abstract SQL statements when there is a perfectly good tool to create queries called the query designer.
Unless it's a pretty simple query I don't try and write them off the top of my head, it's not worth the effort.

Create the query in the query designer PLEASE !!

Secondly, Why are you not creating a calculated field in a query and using that to populate a form.

And you seem to have ignored previous advice about storing these lookup values in a table to simplify things?

Minty said:

You code is not even close to a executable query, or a select query, or a sensible bit of VBA!

Being a little blunt here - but why do you insist on trying to create abstract SQL statements when there is a perfectly good tool to create queries called the query designer.
Unless it's a pretty simple query I don't try and write them off the top of my head, it's not worth the effort.

Create the query in the query designer PLEASE !!

Secondly, Why are you not creating a calculated field in a query and using that to populate a form.

And you seem to have ignored previous advice about storing these lookup values in a table to simplify things?

Click to expand...

I have already created a query, but i has no idea on how to link the query to my form so that i can automate my calculation for my grossweight, that why i choose to do it in the SQL way.

You can take a look at the file i attached below the queries is Grossweight

Where is your query - it's not in the database?

Also your data in your tables is not stored in a way that can be used. Access doesn't understand a text field that has
Below 130
131-199
As data.

Your reference table has values as field names;
These should be stored correctly

You really need to understand some database fundamentals here. Please read the links about normalisation , and table design in my signature and google for further help.

At the moment you are hopping about trying to solve problems that are caused by poor table design and a lack of understanding of how access works. Take a step back and understand what you need to put into a database to get out what you need.

Draw it out on paper. If you can't put it down on paper you can't do it in access.

To give you another hint about your table design, and this is by no means a finished article, I probably would store the mix types in another table...

Have a look at tblGrossWeight in the attached.