Defining field values using vba 1

[dyarwood]

Hi
I know this may sound simple but is there a way of defining a field value using vba?

Taking a field value and the maximum value in the range and using those values in a formula in VBA to give a value. I've got that formula working but want to put that value back into a table.

Also can anyone recommend a book for VBA used within Access.

Cheers

dyarwood

 

[SQLBI]

Hi,

Firstly i would suggest using ADO (ActiveX Data Objects)to poulate the table.

In your code, define two new variables;

Dim cnn As ADODB.Connection
Dim strSQL As String

Then set your ADO connection to that of your Access db;

Set cnn = CurrentProject.Connection

Next define strSQL as an SQL Update statement;

strSQL = "UPDATE tblYourTable SET YourValue = " & YourVariable & ";"

Than execute the SQL statement using your ADO connection;

cnn.Execute strSQL

This is a very crude example, but if you want to post your existing VBA and your table field names etc i'll see if i can adapt your code to what you need.

With regards to a book for Access VBA, i find the Access 2002 VBA Handbook to be invaluable, search Amazon for this.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[dyarwood]

My code is very simple at the minute. Just starting out in Access VBA and trying to get used to it.


Dim code As String

Input box for entering a code which will be used at a later date

code = InputBox(Prompt:="Enter national account&quot

Standard Cost is contained within the table TblMastProd Cost

At the min I am just finding the maximum value contained within Standard Cost and letting x equal that value.

Dim x As Double
x = DMax("[Standard Cost]", "TblMastProd Cost&quot

Then I set y to be equal to the first value within Standard Cost. I will eventually use a loop to go down the list (assuming it is a similar process to Excel VBA)

y = DFirst("[Standard Cost]", "TblMastProd Cost&quot

w = 9 * y / x

Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = CurrentProject.Connection

I am wanting the value w to be entered in Cost Value in the table TblMastProd Cost

strSQL = "UPDATE [TblMastProd Cost] SET [Cost Value] = " & w & ";"
cnn.Execute strSQL

Cheers

dyarwood

 

[dyarwood]

Just changed my coding a little and managed to get it working. Cheers for your help leighmoore. Much appreciated.

dyarwood

 

[SQLBI]

No problem, glad you're all sorted.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

Just one thing,

Your strSQL as it's shown in your code will alter all the values in the table to equal 'w'. If you only want to change a specific record, you'll need to add a WHERE clause into your SQL statement.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk