pass form item value in VBS sql

[newaccess]

Hi Friends,
I got a box of enter parameter value that requests to manual enter parameter value again
when I clicked submit buton. It works when i enter value into parameter box.
However, I already put data in each text fild and saw them by msgbox.
It seems that VBA does not reconiges theselocal declared parameter in RUNSQL command.
please help mw which wrong is about my code?

Thanks
newaccess

Private Sub Command20_Click()
Dim dbExercise As DAO.Database
Dim rstNWD As DAO.Recordset
Dim sale_CODE As Long
Dim sale_NUMBER As String
Dim sale_TYPE As String

Set dbExercise = CurrentDb
Set rstNWD = dbExercise.OpenRecordset("sale_transactions")

rstNWD.AddNew

sale_CODE = Me.sale_CODE
sale_NUMBER = Me.sale_NUMBER
sale_TYPE = Me.sale_TYPE

MsgBox sale_CODE
' inset data
DoCmd.RunSQL "insert into sale_TRANSACTIONS (sale_CODE, sale_NUMBER, sale_TYPE) values " & _
('sale_CODE', 'sale_NUMBER', 'sale_TYPE');"

MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Set rstNWD = Nothing
Set dbExercise = Nothing



MsgBox "test add date "
End Sub

 

[boblarson]

What is the EXACT prompt that comes up?

 

[newaccess]

Hi Bob,
I got a prompt of enter parameter value and display parameter name sale_CODE

Thanks for your help!

 

[newaccess]

Hi Bob,

Please review my attached pict for this issue ( enter parameter value)

Thanks for your help!
newaccess

 

[boblarson]

It would appear that you need to add something:

[COLOR=red]rstNWD![/COLOR]sale_CODE = Me.sale_CODE
[COLOR=red]rstNWD![/COLOR]sale_NUMBER = Me.sale_NUMBER
[COLOR=red]rstNWD![/COLOR]sale_TYPE = Me.sale_TYPE

or the shortened version:

[COLOR=red]With rstNWD[/COLOR]
[COLOR=red]   ![/COLOR]sale_CODE = Me.sale_CODE
[COLOR=red]   ![/COLOR]sale_NUMBER = Me.sale_NUMBER
[COLOR=red]   ![/COLOR]sale_TYPE = Me.sale_TYPE
[COLOR=red]End With[/COLOR]

 

[boblarson]

Oh, and you will need the

rstNWD.Update

after setting those.


But why are you trying to add them twice? You have it going in with the recordset and then you are using an Insert Query which makes NO SENSE whatsoever.

 

[boblarson]

Ok, I just read it some more and it gets stranger.

1. You need to decide how you want to update this. Do you want to use the recordset or the insert query.

2. You should not have variables which have the same names as your fields.

3. You don't put quotes around variable names so the query would need to have them concatenated in.


So, anyway, waiting for your response.

 

[newaccess]

Hi Bob,

Thanks for your help.
However, I still get parameter message display as
' inset data
DoCmd.RunSQL "insert into sale_TRANSACTIONS (sale_CODE, sale_NUMBER, sale_TYPE) values " & _
(sale_CODE, sale_NUMBER, 'ale_TYPE);"
It seems that we are not able to pass local parameter value in VBA code. is it wrong?
I also test your way to update recordset.
I will let you know later.

Newaccess

 

[boblarson]

It seems that we are not able to pass local parameter value in VBA code. is it wrong?

Yes, you are wrong - you can do it. But again - you have your variables named the same as your fields. DO NOT DO THAT. Rename them!

 

[newaccess]

Hi Bob,
I changed as
DoCmd.RunSQL "insert into sale_TRANSACTIONS (sale_CODE, sale_NUMBER, sale_TYPE) values " & _
(str_sale_CODE, str_sale_NUMBER, str_sale_TYPE);"
it does not work.
But it works under recordset way. However I have a issue to auto add sequesce number at sale_number column.

before added new value into sale talbe, I need to find max sale_number and plus 1 as new sale_number pass to with rst and end rst.
i am learn how to do this. do you have idel for this action?
Thanks very much!

Newaccess

 

[boblarson]

So is Sale_Number just a Long Integer field which you then add the number? It isn't an Autonumber is it?

 

[newaccess]

Hi Bob,

The table is a oracle table and Sale_Number is a character data type.
I can get a max (Sale_Number) by access sql. (select max(Sale_Number ) from sale But I do not know how assign SQL return value into a local parameter in VBS codes.

any example?

Thanks
Newaccess

 

[boblarson]

Is the Oracle table a linked table?

 

[newaccess]

Hi Bob,
It is a the Oracle linked table.

How do I get a max sale_number in VBA side?

Thanks
Newaccess

 

[boblarson]

It is a SELECT statement then, just like any other table:

Dim strSQL As String
 
strSQL = "INSERT INTO sale_TRANSACTIONS (sale_CODE, sale_NUMBER, sale_TYPE) Select " & _
OracleTableNameHere.sale_CODE, OracleTableNameHere.sale_NUMBER, OracleTableNameHere.sale_TYPE FROM OracleTableNameHere;"

 
CurrentDb.Execute strSQL, dbFailOnError

 

[newaccess]

Hi Bob,

I just need to get a return mac sale_number value from sql in VBA code and then assign it to local variables.


How can I do that ?

Thanks
Newaccess

 

[boblarson]

Are you talking about a single record or multiples?

 

[newaccess]

Hi Bob,

Thanks for your help.
I just need single max value (sale_number columm ) from a linkedtable.

Thanks

Newaccess