Change table field from multiple forms based on another field

[Stephenson]

Hello all,

I'm attempting to build a database for the tracking of construction equipment. There is table, tblEqipment that holds all the pertinent information for the equipment. Eventually there will be multiple forms e.g., Service, Repair Order, Mobilization... The current use amount (hours or miles on the equipment) is stored in tblEquipment. I would like each of these forms to display the most current record for the equipment use amount in tboEquipmentUse. The user would then update tboEquipmentUse with the actual current use amount. I would need to record the current use amount in the forms corresponding table as well as update tblEquipment's field for current use amount.

Currently there's a combobox on my forms for the equipment. The control source is EquipmentID. I would like tboEquipmentUse to show the record for equipment use amount from tblEquipment as well as be able to be adjusted by the user. I assume I will need a second text box to record the adjusted value to record in the table for the form being used.

Please let me know if there are any good ways to show the value of the equipment's current use based on the selection of a combobox, then allow a user to update it from multiple forms as well as record the value in the forms corresponding table.



Thank you for looking,

John

[nrgins]

In the AfterUpdate event of the form in which the user enters a tboEquipementUse record, have it update the corresponding tblEquipment record, either using an update query or in code.

[Stephenson]

Thank you for the input nrgins.

I'm trying to learn how to accomplish what you've suggested, but am falling short. I've researched/learned about VBA Update and Insert commands and am still not clear on the best way to use them.

I know I'll have to update the record in tblEquipment based on the ID from cboEquipment, but can't find any information on how to designate this.

Could you give me some more information on using update/insert commands and what will work better for my case?

Thanks for the help.

[nrgins]

Actually, I just reread your note and it sounds like you want tblEquipment fields in your tboEquipmentUse form.


So, if I'm understanding it correctly:


* You have a table, tblEquipment, that lists equipment.


* You have another table, tboEquipmentUse that lists info about equipment usage.


* You want to display info from both tables together.


Two ways you can do this:


1) Have a form for tblEquipment and a subform for tboEquipmentUse which are linked on EquipmentID. That will display info from both forms. When you create a tboEquipmentUse record, Access will automatically insert the EquipmentID value into the tboEquipmentUse record. Then, you would put the following in the subform's After Update event:

Me.Parent![TotalUse] = Nz(Me.Parent![TotalUse], 0) + Nz(Me.[CurrentUse], 0)
Docmd.RunCmd acCmdSaveRecord

2) The other way would be to have a single form with fields from both tables in it. In your form's Record Source, you would add both tables, creating an outer join from tblEquipment to tboEquipmentUse (all records from tblEquipment and only matching records from tboEquipmentUse). You would then add fields from both tables to the form, setting the tblEquipment fields to Locked = True (since you don't want the user to be able to modify the tblEquipment fields in this form). Then, in the form's Before Update event (not After Update this time), you'd put:

Me.[TotalUse] = Nz(Me.[TotalUse], 0) + Nz(Me.[CurrentUse], 0)

(Change "TotalUse" and "CurrentUse" to whatever the names of your fields are.)

[Stephenson]

nrgins,

Thanks for the speedy and thorough reply.

You are correct that I have a table, tblequipment. tboEquipmentUse is however a text box on my form, frmRepairOrder. This form, frmRepairOrder uses table, tblEquipmentReapirOrder as its record source. I will have multiple forms similar to this that I would like to display the most current equipment hours/miles as well as allow the user to update them.

For example: Our mechanic services our backhoe. He would enter the service done in the form frmService that uses table tblEquipmentServiceLog as a record. Within form frmService there would be a text box tboEquipmentUse that would display the backhoe's latest usage entry once selected from a combo box cboEquipment. The mechanic would then update if needed cboEquipment to the hours/miles at the time of the service. This number would then update table tblEquipment to the most current hours/miles as well as record in table tblEquipmentService when the service was done.

I'm lost on how best to handle the information gathered in my text box tboEquipmentUse. I don't know the best way to show the information from one table based on a text box then update two tables once the form holding this text box has been updated.

I hope my question is a tad bit more clear at this point.

Thanks,

John

[nrgins]

Are you asking how to set up the form and then update the field in tblEquipment? Or is your form already set up and you just want to update the field? You're not being clear on what's going on here.

If you want to set up your form with data from both tables, then do what I outlined in #2, above. If your form is already set up, and, for some reason, that doesn't apply, and you want to just update the values in tblEquipment and tblEquipmentService, then you can, in the form's After Update event, put:

dim strSQL as string

strSQL = "Update tblEquipment " & _
         "Set CurrentUsage=" & me.tboEquipmentUse & _
         " Where EquipmentID=" & me.EquipmentID
Currentdb.execute strSQL, dbfailonerror


strSQL = "Update tblEquipmentService  " & _
         "Set ServiceDate=#" & vba.date & "# " & _
         "Where EquipmentID=" & me.EquipmentID
Currentdb.execute strSQL, dbfailonerror

If that's still not clear, or if that doesn't apply, then you'll have to be more specific as to how your form is set up and what specifically doesn't apply from what I wrote or what is still unclear. Because right now, I think I've answered your question, and if you still have a question then I'm not understanding what it is.

[Stephenson]

nrgins,

Thank you for your help I've got a working form using the following,

Private Sub cboEquipment_AfterUpdate()Me.tboCurrentUse.Value = DLookup("[CurrentUsage]", "tblEquipment", "EquipmentID=" & [cboEquipment])
End Sub

And

Private Sub Form_AfterUpdate()
Dim strSQL As String


strSQL = "Update tblEquipment " & _
         "Set CurrentUsage=" & Me.tboCurrentUse & _
         " Where EquipmentID=" & Me.EquipmentID
CurrentDb.Execute strSQL, dbFailOnError


End Sub

I'm looking closer at your suggestion #2 and am going to keep trying to get it to work. Currently I'm having trouble with doing an outer join because table, tblEquipment has a one to many relationship with tblRepairOrder. I have EqipmentID as a foreign key in tblRepairOrder. When I try to create the outer join I get the error message "The SQL statement could not be executed because it contains an ambiguous joins..."

I will do my best to figure this out and post back.

Thank you again for all the help.

[nrgins]

I don't mind helping if you have specific questions like that. I was just saying that I didn't know what else to suggest.

Ambiguous outer joins happens usually when you have an outer join followed by an inner join. You can't do that. You have to make all joins after the outer join outer joins in the same direction. There are other issues that cause it as well; but that's the usual one.

If you still can't figure it out, then post a screen shot of your query in design view, and I'll take a look at it.

Also, if you have problems with other areas, such as your code, be sure to post the code or a screen shot.

[Stephenson]

Thank you nrgins. I really appreciate that.

I do have questions about successfully executing an outer join. I will look at it for a bit and post a pic or to for you to look at.

Again, thank you very much for the help.

[nrgins]

I looked back at my earlier text, and I gave you wrong information. You want an outer join from tblRepairOrder to tblEquipment, not the other way around. Sorry about that!

Since tblRepairOrder is the table you're entering data into, you want all records from tblRepairOrder and only matching records from tblEquipment.

Then, still in your query, select the * from tblRepairOrder to get all fields, and then only the fields you need from tblEquipment (including the one that's going to be updated). But don't include EqipmentID from tblEquipemnt in your query. You'll be using the EqipmentID field from tblRepairOrder instead, and you don't want two fields with the same name.

Then, on your form, add the fields that you want on the form. For EqipmentID (from tblRepairOrder), make it a combo box, and set the combo box's Row Source to be EqipmentID values from tblEquipment. (You can add other fields from tblEquipment as a 2nd or 3rd column or whatever in the combo box, so you can see them in the list when you select an EqipmentID value, if you want.) This way, the user will select the EqipmentID value for the tblRepairOrder.EqipmentID field from the list of available values, so they can't enter one that doesn't exist in tblEquipment.

When the user selects an EqipmentID value from the combo box, all the fields from tblEquipment will be automatically populated, because the relationship between the tables will be established on that value.

Set all the fields from tblEquipment to be Locked=True (so that the user can't edit them). I also set the background of those fields to be Transparent, so they have the same color as the form background, instead of white. That makes it clear to the user that those fields aren't for editing. I also remove the tab stop from those fields (though the user can still click in them with the mouse if they want to copy the info).

So that should get you what you need. Hopefully reversing the direction of the outer join will resolve your "ambiguous outer join" issue. If not, then let me know.

[Stephenson]

Excellent!

I'm going to give it a shot.

[Stephenson]

This is great nrgins. Everything looks to working well and I've learned about outer joins. This will be very useful in my quest.

Thank you for getting me over this hurdle.
Now onto the next.

[nrgins]

Great! Glad it worked out for you!