Update data in a table via a form

[SeanA]

Hello everyone,

I have a table with REG#, Service Bulletin, Work Order, and Completion date fields. I am building a form that will pull the current values from that table and allow Work Order and Completion date to be edited. I then want to be able to save the current record and have it update the original table. The format of my form is as follows:

Combo box for REG# (user inputs an REG# and moves to Service bulletin)
Combo box for service bulletin (a cascading combo box that shows all available service bulletins, user selects which one they are trying to update)

From here I will have a box for the work order and completion date. I want the use to be able to input a work order and a completion date and be able to save/update the original table.



Let me know if you have any ideas.

Thanks,

SeanA

[June7]

If form is bound to source table or query, data edits will be passed directly to record in table. Just need to go to the record on form. Here is one method http://datapigtechnologies.com/flash...tomfilter.html

[SeanA]

That video doesn't really show what I am trying to do. I know how to bring the records up using combo boxes. What I am looking for is a way to bring up the current data (from a table) that allows for the data inside of the boxes (except for REG# and Service Bulletin) to be editable. I then want to be able to hit a "Save and Update" button that will update the other boxes, Work Order and Completion, inside of the original table for the associated REG# and Service Bulletin.

[SeanA]

Correcton, I did not solve it. Le sigh.

[June7]

Don't understand what you mean by 'bring up the current info ...'.

If you want to provide db for analysis, follow instructions at bottom of my post.

[SeanA]

What I meant by "bring up current info" is I have date in a table. I would like to have a form the can bring up the date for a certain REG# and Service Bulletin that allows for the work order field to be edited and the completion date field to be eidted. I then want to be able to hit a save button and it update the data in the table.

I have attached a db file. The two tables, servicebulletins and Vehicles give contain the data. The form "New Service Bulletins" is the form I want to be able to input the data in and be able to save it in the table "servicebulletins" (I do not want it to save a new entry, I want it to overwrite the current data for work order and completion date).

Thanks!

[June7]

I get that. You need to filter the New Service Bulletins form to specific Reg# record and edit the Work Order and Completion Date fields. The link I posted demonstrates one method to accomplish that. Make the recordSource for the form a parameterized query as shown in the tutorial. Reg# is a unique value and really the only field that needs filter criteria to select single record. You might find multi-column combobox less complicated than the dependent comboboxes. http://datapigtechnologies.com/flash...combobox3.html

There are other methods with different VBA code. Otherwise, use the intrinsic Access search/filter tools and no code is required.

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in any names nor reserved words as names.

[SeanA]

I'm sorry, but I may still be missing something. That did not help me much. I'm at the same point as before. I have gotten my data to save, but everytime I hit save it will replace the data in the first fow of my data table. Not sure what to do at this point.

Thanks for the tip on the spaces/special characters.

[SeanA]

June,

I also should point out that my table list a REG# multiple times. The filter criteria I would need is by REG# and ServiceBulletin. Not sure if this was the best way to do it or not.

[SeanA]

This is where I am at:

Private Sub SaveRecord_Click()

DoCmd.RunSQL "UPDATE ServiceBulletins SET ServiceBulletins.WorkOrder = " & Forms!NewServiceBulletins!WOCombo
        
End Sub

So, this kind of works. The problem is this updates all of the data inmy ServiceBulletins table (for WorkOrder). What I want is to update a specific record for a certain REG# and ServiceBulletin. I'm thinking I can use a WHERE statement in my update, but I have been unable to get that to work. What I have been trying is similar to:

Private Sub SaveRecord_Click()

DoCmd.RunSQL "UPDATE ServiceBulletins SET ServiceBulletins.WorkOrder = " & Forms!NewServiceBulletins!WOCombo WHERE ServiceBulletins.REG# = Forms!NewServiceBulletin.RegCombo2 & ServiceBulletins.ServiceBulletin = Forms!NewServiceBulletin.ServBullCombo

End Sub

The bolded/underlined is the criteria I am trying to filter by. Have not been able to get this to work.

Thanks

[June7]

It replaces data in first row because that is the record the form is sitting on.

Why on earth would you use an UPDATE query as opposed to simply opening bound form to the desired record and typing the edits into controls? Entry/Edit to bound controls will pass directly to record in table.

WHERE ServiceBulletins.[REG#] = Forms!NewServiceBulletin.RegCombo2 AND ServiceBulletins.ServiceBulletin = Forms!NewServiceBulletin.ServBullCombo

[alcapps]

I took a quick look at the tables. In addition to june7. I would make that update form a datasheet view of the whole table like june7 suggested. Also you can remove the yes/no complete field and use the Complete date field. if you have a date in the field the record is complete. if it is null then it is not complete. This way you can remove the extra field in the database and retain the same results.

[SeanA]

Ok, dreamcrushers! (joking)

What I have switched to is a form in datasheet form that is bound to a query that updates the table. I have a new problem now. On my form, I want to have two combo boxes, REG (i removed the REG# from the database) and Service Bulletin. I would like for the user to first select a REG and the table update for all values that have that REG. I would then like to be able to filter again using the "Service Bulletin". So, if I wanted to see all records for REG 80L, I would select 80L in the combo box. If I wanted to see the record for 80L, Service Bulletin MA-18, I would make the selections in the two combo boxes and the query would update for those values.

Currently, I am using the criteria selection in the query, but I have been unable to do this correctly.

Thanks again for all of the help so far!

[June7]

Run an UPDATE sql action.

Since you want the update to be dynamic for 2 criteria, I would use VBA to construct the SQL statement and then execute the SQL. Something like:

strSQL = IIf(IsNull(Me.cbxREG), "", "REG='" & Me.cbxReg & "'")
strSQL = strSQL & IIf(IsNull(Me.cbxBull), "", IIf(strSQL="", "", " AND ") & "ServiceBulletin='" & Me.cbxBull & "'")
CurrentDb.Execute "UPDATE tablename SET fieldname = somevalue " & IIf(strSQL="", "", " WHERE " & strSQL)

[alcapps]

yes have two combo boxes..
cboReg and cboSrvBull both have distinct values in them.

like June7 said use VB to make this happen.
make a button cmdUpdateValues
on Click event
add
dim db as DAO.database
dim strSQL as string
dim strWhere as string
dim ans as variant

'check for Values in Combo boxes
if Len(Trim(nz(me.cboReg,""))) = 0 then
msgbox "please select a Reg value",vbOkonly,"Need to Select a Value"
me.cboReg.Setfocus
end if
if Len(Trim(nz(me.cboSrvBull,""))) = 0 then
msgbox "please select a SService Bulletin alue",vbOkonly,"Need to Select a Value"
me.cboSrvBull.Setfocus
end if

'double check that they really want to do the update
ans = msgbox("Are you sure you want to Update the values?", vbYesNo, "Are you sure?")
if ans = vbNo then
exit sub
end if

'set the db variable
set db = currentdb()

'Next create the SQL statement to run
strSQL = "UPDATE tablename SET fieldname = somevalue, fieldname2 = somevalue "
strWhere = " Where Reg = '" & me.cboReg & "' and ServiceBulletin = '" & me.cboSrvBull & "'"


db.execute strSQL & strWhere, dbFailOnError


msgbox "All set"




hope this gives you some ideas