Button on Form

[jo15765]

I have a form, where you input the customer #, and the zip code. And from there you can do multiple items. I want to add a button that says "Finish" and have the onclick() event for the Finish button run an update query (local query on a linked table, since I Couldn't find VBA code to do). However, the issue I am running into is I need to somehow be able to tie the customer # that is input on the form, BACK to the table, so that the correct account information is updated, and not just update off of zip (that would be a HUGE error!!!) How can I tie the customer # that is input on the form, back to my linked tbl_main customer # so that the "Finish" button will update the correct account?

Does that make sense?

[pkstormy]

Add criteria in your update query as such under your Customer# field: =Forms!MyFormName!InputCustomer#FieldName

[June7]

The saved query would refer to the control on the form in the WHERE cell under the CustomerNo field: Forms!formname!controlname
Then in VBA can run the saved query:

DoCmd.SetWarnings = False

Currentdb.Execute "query name"

or
DoCmd.OpenQuery "query name"

or instead of running a saved query
DoCmd.RunSQL "UPDATE tablename SET fieldname1=value1, fieldname2=value2 WHERE CustomerNo='" & Me.controlname & "'"

DoCmd.SetWarnings = True

If value1, value2, etc come from controls on form, would have to concatenate them also. Apostrophe delimiters if field is text datatype, # if date, nothing if number.

[jo15765]

That looks like it will do the trick. Do you by chance happen to know what the SQL of that query would be?

[June7]

Don't understand your question, the example is there, tailor it for your data.

I did do some edits to the post. Look at it again.

[khalid]

Add criteria in your update query as such under your Customer# field: =Forms!MyFormName!InputCustomer#FieldName

look like a star tracks episodes

[jo15765]

I was running a saved query in the db, because the table I need to update is actually in a totally seperate db. Is there a way to update the table w/o linking it in?

pkstormy, the =Forms!MyFormName!InputCustomer#FieldName would this go in the criteria portion of my query? I want to make sure I understand this correctly?

june7 ----
Is there a way to run this line of code...
DoCmd.RunSQL "UPDATE tablename SET fieldname1=value1, fieldname2=value2 WHERE CustomerNo='" & Me.controlname & "'"
if the table is in a seperate db, or will this only work if i link the table in?

[pkstormy]

Yes - in the criteria portion under the customer # field. But note that you need to substitute the dummy name form/field name in the example with your real form name and field name (and the form would need to be open/on the customer # record for the query to work).

[jo15765]

Oh lord, I copied it exactly and it is not writing records to my table...I set it as an update query, and here is the SQL for that update query.....

UPDATE tbl123 SET tbl123.status = [Enter Status]
WHERE (((tbl123.program)=[Forms]![form1]![ProgramName]));

I have the user inputting the status, and the program being pulled from the textbox on the form.

But for whatever reason, it is not writing the data, it says I am about to append 0 rows?????????????

[June7]

Yes, can programmatically transfer data between files. Here is code where data is pulled from another project. I haven't done a procedure to push data but expect could work.

Private Sub GetPaverData()
Dim strSourcePath As String
Dim strSourceFields As String
Dim strDestFields As String
Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("\\dotatufs02\airports\Report Production\GIS\Airports.mdb")
A.DoCmd.RunMacro "PaverDataM"
Set A = Nothing
strSourcePath = "\\dotatufs02\airports\Report Production\PaverDB_All\pavement.mdb"
strSourceFields = "ID, [Name], Condition, [_Latest], Source, Use, Include, Active, [Date], Area, FAAID"
strDestFields = "SectionID, BranchName, Condition, Latest, Source, Use, Include, Active, InspectionDate, Area, FAAID"
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM PaverData_InspectionsAllYears"
DoCmd.RunSQL "DELETE FROM PaverData_MajorMRAllYears"
DoCmd.RunSQL "INSERT INTO PaverData_InspectionsAllYears(" & strDestFields & ") SELECT " & strSourceFields & " FROM [" & strSourcePath & "].zUser_InspectionsAllYears;"
DoCmd.RunSQL "INSERT INTO PaverData_MajorMRAllYears(SectionID, BranchName, Use, ConstDate, Include, Active, FAAID) SELECT ID, [Name], Use, [Date], Include, Active, FAAID FROM [" & strSourcePath & "].zUser_MajorMRAllYears;"
DoCmd.SetWarnings True
End Sub

I don't use parameter prompts in queries. I have user enter value on form and refer to the form control. I also don't use query objects for INSERT and UPDATE, only VBA. However, I can't see anything wrong with the SQL you have.