Question on updating table using VB coding

[ChoonHong]

Hi, i have a problem on the SQL Query. Now i have a unbound textbox which i had created on "Forms". The question is this, am i able to update the integer in the unbound textbox using VB commands?

I know that there is a command called DoCmd.RunSql, how do i use it? I tried to look at the help files at Access but it did not give me any help. Hope that someone could assist me.

 

[easyit]

Hi,

Docmd.Runsql accepts a string (sql statement). Docmd.OpenQuery accepts an existing query. In both cases you can put a field of a form in the query.

For more info I suggest that you highlight a staement and press the F1 key.

EasyIT

"Do you think thats air you're breathing?

 

[ChoonHong]

Hi, as i do not have my "VB" help installed i can't use this function.

Now i have created a function bound to a textbox and "Calculate" button to update my table. But it always prompt me to enter a value on the textbox and say that there is no value in the table. I have created a table for my textbox to hold my parameters but it still prompt me to enter a value.

This is the coding for my Calculate function and function for update. Can anyone help me on this problem?



Private Sub btnCalculate_Click()

RemainderUnits = txtboxInitial.Value - txtDrawUnit.Value
RemainderUnits_AfterUpdate

End Sub


Private Sub RemainderUnits_AfterUpdate()

Dim strSQL As String
strSQL = "Update Inventory SET Inventory.Initial_Store_Units = " & RemainderUnits & " Where Inventory.Inventory_ID = Inventory_ID "

DoCmd.RunSQL strSQL
DoCmd.Requery (Initial_Store_Units)
End Sub

 

[easyit]

IN SHORT
you can build the query as follows:

open your form;
create a new query;
insert the necessary . table(s)
set the cursor in the criteriafield
press ctrl+F2 to open the expressionbuilder
Now you can select the form and the formfields.
build the query as needed. When finished, go to sql view in the query.

you can now copy paste the SQL to VBA OR: you just save the query and use the queryname (docmd.openquery "myQueryName"

When you copy the sql you have to check the syntax, because the string should be slightly different in VBA - you'll see the errors.

good luck

EasyIT

EasyIT

"Do you think that’s air you're breathing?

 

[PHV]

i do not have my "VB" help installed
So, please, install it and use it.
This forum is not a help desk and you're supposed to have r
at least search your help files before posting !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886