putting result of prompt in MsgBox

[folkie]

I have a command button that imports a text file. Since this file doesn't have a month field, I also run an update query which puts the month (based on what the user enters when prompted, which comes from the qUpdateMonth query) in that field. Breifly, I do this with the following code:

DoCmd.TransferText acImportFixed, "ImportCPC", "tCPC", txtFilePath
DoCmd.OpenQuery qUpdateMonth, acViewNormal
MsgBox "You updated the table for the month " + [qUpdateMonth].[StmtMonthYear], vbYesNo, "Report Table Update"
DoCmd.Close

StmtMonthYear is the field whose value from the prompt in the qUpdateMonth query I would like to see in the MsgBox. I've tried all kinds of ways (including an ampersand) besides the plus sign and bracketts and dots, but I keep getting the message "CPC can't find the field '|' referred to in your expresion." CPC is the name of the database.

Is there a way to get that value (the user enters when prompted) as part of the text (or maybe the title bar if it can't be in the text) in the MsgBox, and if so, do you know the syntax?

 

[Uncle Gizmo]

If I Have read your question correctly, I think you are trying to grab information out of a query that is in view. I don't think you can do that?

What I would suggest is you modify your code so that when you activate the command button it pops up and "input" box where you can ask the user for the date information you want. Then you need to pass that information back to a variable in the code behind the form, alternatively you could put the information in a control on the form. However are think variable would be the best place. Now when you run your query grab the information from the variable or the control.

 

[ghudson]

How do you account for when the user misspells the month that they key into the input box when the query is called?

I suggest that you add a combo box to your form and let the user select the month. Have your update query reference the combo box. Then have your message box reference the combo box to display the month selected.

 

[folkie]

I was starting slow by, first, trying to figure out how to have the user see what he/she entered after it was entered. Then, I would give the user the option to not run the update query if the wrong date was entered. I think I know how to do the latter, but I'm not sure how to do the former. I'll try the combo box for selecting the month and the rest of your suggestion (though I may have questions about that when I try it).

 

[folkie]

Using a list box where the user selects the month & year, I was able to have the user select the month & year and have that month & year appear in the message box. However, after trying all kinds of things, I can't figure out how to pass that month & year to the update query. The best I come up with is the field gets updated with a slash instead of the month slash year, probably because the field StmtMonthYr (which contains the value selected) is defined as "[Month] & '/' & [Year]" Here are a couple lines of code:

MsgBox("are you sure you want to use " & lboxSelectMonth & " ?", vbYesNo, "Report Table Update")
DoCmd.TransferText acImportFixed, "ImportCPC", "tCPC", txtFilePath
DoCmd.OpenQuery qUpdateNewOne, acViewNormal

lboxSelectMonth (the name of the list box from which the user selects the month and year) has the correct value of the month and year

qUpdateNewOne is my latest version of the update query; in it, the value of StmtMonthYr updates the field in the tCPC table; originally StmtMonthYr came from a paramter query which was used by qUpdateNewOne; that worked fine for updating the table (but I wasn't able to get that month & year into the MsgBox); now I'm using a non-parameter query (& getting that value in lboxSelectMonth and in the MsgBox), but I can't get that month & year selected to update the field in the table

Any detailed instructions would be appreciated.