append query and variables from a memo field

Hi,

I have string data from a memo field in a form that is parsed some of the data is stored in variables when a button is clicked. These values are not saved, and do not appear in any of the fields in the form. How do I reference and use these variables in an append query?

Thanks for your help,

Bill

The following example will parse the first word from a memo field named Text and will append to Table1 field AppendedText behind a command button

Dim strSQL As String
strSQL = "INSERT INTO Table1 ( AppendedText )" & _
"SELECT Left(Text, InStr(1, Text, "" "") - 1) AS BOGUS;"
DoCmd.RunSQL strSQL

You can also assign the value of an unbound textbox with your string parsing and use the unbound textbox in your append query as the field and run the query directly while the form is open....or

You can create a recordset of the table and use the .Add & .Update methods of the recordset

Was this what you were looking for???

lwells

Thanks! This is getting close. This is what I have:


varTitle = Mid(varPasteString, varBeginTitle, varEndTitle - varBeginTitle)

varItemID = Mid(varPasteString, varBeginItemID,

varCallNumber = Mid(varPasteString, varEndItemID, 25)

Now I would like to use the values stored in these variables in an append query.

The paste field from which these values are retrieved is a bound field on a form. My sub goes through and retrieves the values above. How do I reference them so that I can use these variables in a query? Is this possible?

Thanks for your help,

bill

For simplicity I would assign an unbound textbox to hold your variable on the form.

For example place a unbound textbox on your form (Hidden) and give it an appropriate name.

varTitle = Mid(varPasteString, varBeginTitle, varEndTitle - varBeginTitle)
Textbox1 = varTitle
Then modify the above sql slightly

Dim strSQL As String
strSQL = "INSERT INTO Table1 ( AppendedText )" & _
"SELECT [Textbox1] AS BOGUS;"
DoCmd.RunSQL strSQL

Use the same procedure for your other two variables.

lwells

thanks lwells. using hidden text boxes and setting their value to the variables worked.

many thanks,

bill