Store input box variable in field in table

[Snowflake68]

I have some vba that prompts the user for a value to use a s filename. How do I also store this value in a field called {CustFileName] name' in table "SelectedCustomer".

Dim strCustomerName

 strCustFileName = InputBox("'What do you want to call the file? Press enter to accept customer name as filename", "Customer File Name", DLookup("[CustName]", "SelectedCustomer"))

I have tried to use just a simple update query instead to prompt from the input value but I would really like to display a value in the prompt box so thought an input box using VBA would be better.

Hope someone can help.

Thanks in advance.

 

[Minty]

Create a the same update query in VBA and execute it using the input box value. Something like

Dim strCustomerName
Dim sSql As String

 strCustFileName = InputBox("'What do you want to call the file? Press enter to accept customer name as filename", "Customer File Name", DLookup("[CustName]", "SelectedCustomer"))

sSql = "UPDATE SelectedCustomer "
sSql = sSql & "SET  CustFileName = '" & strCustFileName & "' "
sSql = sSql & "WHERE [COLOR="Green"]YourCriteriaGoesInHere [/COLOR] "

debug.print sSql

currentdb.execute sSql

 

[Snowflake68]

Create a the same update query in VBA and execute it using the input box value. Something like

Dim strCustomerName
Dim sSql As String

 strCustFileName = InputBox("'What do you want to call the file? Press enter to accept customer name as filename", "Customer File Name", DLookup("[CustName]", "SelectedCustomer"))

sSql = "UPDATE SelectedCustomer "
sSql = sSql & "SET  CustFileName = '" & strCustFileName & "' "
sSql = sSql & "WHERE [COLOR="Green"]YourCriteriaGoesInHere [/COLOR] "

debug.print sSql

currentdb.execute sSql

Cool thanks Ill give it a go and let you know how I get on.

 

[Snowflake68]

Minty you are a Diamond Geezer. This works perfectly. Once again you have helped a Damsel in distress. I am learning so much with your help.
Thanks again x
PS just noticed you are only in the next county from me (Im in Hampshire)

 

[Minty]

You are welcome.
As a more robust process it might be worth adding some code to check they don't enter a null value or any characters that would result in an illegal filename into the input box.

Alternatively you could create the filename using the customer name / code and maybe the date in a format of CustomerCode_yyyymmdd

 

[Snowflake68]

one quick question. How would i write the criteria for the WHERE clause if I wanted to only make the update if the field was null?

 

[Minty]

Something along the lines of

 "WHERE YourField IS NULL [COLOR="Green"]AND Your Other criteria in here[/COLOR] "

Would do it