insert into a table from a msgbox input

[mendesj1]

trying to take what a user enters into a msg box and insert that value into a new record in a table

Dim SetPrice As Currency

SetPrice = InputBox(Prompt:="Enter New Price Base.", _
Title:="New Price Base", Default:="Enter here")
CurrentDb.Execute "INSERT INTO [PrototypeDatabaseCafe2010].[dbo].[PriceBasis]" ([PriceBase]) Values (& Setprice.Value & ) "


thanks guys

 

[Alansidman]

Look here. Last half of the video explains.

http://www.datapigtechnologies.com/flashfiles/msgboxinputbox.html

Step missing is that the textbox that is storing the data in the form needs to be bound to a table in order to get the info to the table.

Alan

 

[mendesj1]

thanks for the help so far but still missing how to bound it to a table

 

[Alansidman]

How did you create your form? Did you do it with a wizard and base it upon a table? That would bind it to that table. If you did it manually, you will need to open the properties for the form and set the record source as the table you want to bind it to. It is on the Data Tab for the Form.

Alan

 

[vbaInet]

You are not even validating the input.

 

[mendesj1]

the form is bound to a sub querry

 

[mendesj1]

Still trying to take data entered in a msg box i set the value of that text to set price then try to take that and insert it into a sql table

Private Sub Command45_Click()
Dim SetPrice As Currency

SetPrice = InputBox(Prompt:="Enter New Price Base.", _
Title:="New Price Base", Default:="Enter here")

DoCmd.RunSQL ("INSERT INTO [PrototypeDatabaseCafe2010].[dbo].[PriceBasis]([PriceBase])Values (setprice)")

End Sub

 

[JANR]

You need to refrence your variable outside the Sql-string

Private Sub Command45_Click()
Dim SetPrice As Currency
 
SetPrice = InputBox(Prompt:="Enter New Price Base.", _
Title:="New Price Base", Default:="Enter here")
 
DoCmd.RunSQL "INSERT INTO [PrototypeDatabaseCafe2010].[dbo].([PriceBasis]([PriceBase]) Values [COLOR=red](" & setprice & ")"[/COLOR]
 
End Sub

JR

 

[vbaInet]

And remember that

You are not even validating the input.

is necessary before inserting.

 

[mendesj1]

i tried using that change and now get a runtime error 3134

 

[vbaInet]

What is the meaning of the error code 3134?

 

[mendesj1]

run time 3134
syntax error in insert into statement

 

[vbaInet]

What does each part of this [PrototypeDatabaseCafe2010].[dbo].[PriceBasis] represent?

Is this an ADP?

 

[mendesj1]

that it the specif database on my sql server with table name and colum name

 

[mendesj1]

this inserts the value if i run this querry on my sql server and try to insert 2.54 into the table

INSERT INTO [PrototypeDatabaseCafe2010].[dbo].[PriceBasis]
([PriceBase]
)
VALUES
(2.54)

 

[vbaInet]

I think that RunSQL will only work for Access tables so perhaps you need Currentdb.Execute.

Are these linked tables?

 

[mendesj1]

yes they are linked tables sql backend with an access front end

 

[vbaInet]

Did you try CurrentDb.Execute?

 

[mendesj1]

yes tryed both ways still not working must be missing something small,

 

[vbaInet]

But have you added the PK?