Help designing a group update form

[10 Gauge]

I'm working on a fleet tracking database. What I need to do at the primary users request is have a form that displays all distinct pieces of equipment and will allow him to simply tab through each piece of equipment and add a new usage date & hours or miles.

Something along the lines of:

Unit #  |  Year  |  Make  |  Model  |  Usage Date  |  Usage 
701         2005     Cat      401        (blank/NEW)  (blank/NEW)
702         2008     Chevy    Silverado  (blank/NEW)  (blank/NEW)
etc
etc
etc

What kind of form type would I use for something like this. And then the biggest problem I am seeing is only having it list each individual piece of equipment once + 2 columns to append new data to the table.

TIA for any suggestions.

[10 Gauge]

Bump for any information on how to set this up.

[jzwp11]

I would probably use a continuous form that brings in the base information about the vehicle (asset) making sure to include the assetID primary key value (can be hidden to the user). Since the usage info is actually going into a separate but related table, you will probably have to use 2 unbound textbox controls for the usagedate and the usage amount and run an append query in code. You could probably use the after update event of one of the 2 controls, but maybe a button for the user to push to commit the usage info would be better, your call. In the code behind the after update event or button, you would need to check to make sure the 2 textbox controls have valid data and if so, run the append query to add them to the related table. You will then have to disable that record from another update (perhaps hiding the controls?)

[10 Gauge]

Great info, thank you sir. I have found with unbound text boxes on forms however it changes all of the values of everything listed. Would this stop once I added the Append Query code?

[jzwp11]

I have found with unbound text boxes on forms however it changes all of the values of everything listed. Would this stop once I added the Append Query code?

Unfortunately, not and furthermore, my suggestion of disabling the controls will do it for all records not just the current one.

Another option is to have an unbound form with a list box that shows the assets, 2 textboxes and 1 button. The user could select an asset, put in the usage info in the 2 text boxes and hit the button. The button would then execute the append query and then remove the asset from the list.

This would require a little more complicated query to populate the list box since it will have to evaluate if the most recent usage date is old enough. For example, if you post the usage info every Friday you can compare the current date to the most recent usage date, if it is more than 7 days old then display the asset in the list box. If it is less than 7 days old then it is probably the info that was just entered & should not be displayed. Or you can include the most recent usage info with the asset in the listbox and have the user make the decision.

[10 Gauge]

Alright thanks jz. I will try the first option then since it sounds a bit easier. I appreciate you giving me your help on this!

[jzwp11]

Let me know if you need help getting it set up.

[10 Gauge]

Thanks I appreciate the offer!

When I get in the office on Monday I am going to set up as much as I can and see if I can't start the append query. I will post as much as I was able to do for you to look at.

[10 Gauge]

Good morning jzwp11. I am starting to work on this form and I am trying to do the SQL code string for the update botton click event.

This is what I hacked together for the button code, I haven't tried it as I am sure it probably won't work but I was curious if you would let me know if I am on the right track and/or what to do to make it work?

Private Sub UpdateButton_Click()
Dim mySQL As String
mySQL = "INSERT INTO AssetUsage ([AssetID], [Usage], [UsageDate])"
mySQL = mySQL & " SELECT AssetID, tbUsage, tbUsageDate"
mySQL = mySQL & " FROM Forms![Usage Mass Update]"
End Sub

Where tbUsage and tbUsageDate are the names of the unbound text boxes on the form [Usage Mass Update]. Can you query a form as I have laid out or does something else have to happen?

TIA for your help.

[jzwp11]

No, you cannot query a form, you can only query a table or another query, but you can use form references in the WHERE clause of the query. Additionally, you can use form controls in the VALUES() portion of an append query.


BTW, you spelled INSERT wrong in your code. Since the data you want to append is in a control in the form you need the INSERT INTO...VALUE form of the append query. The VALUES clause is where you bring in the info from the form controls. Are you using a list box for the AssetID?

mySQL = "INSERT INTO AssetUsage (AssetID, [Usage], UsageDate)"
mySQL = mySQL & VALUES (" & me.AssetID? & ", " & [COLOR=blue]me.tbUsage & ", #" & me.tbUsageDate & "#)"

[10 Gauge]

Ok thanks, I will now play with it some more.

Are you using a list box for the AssetID?

I'm using a continuous form (so a text box I guess?).

[jzwp11]

I thought that you were not going to go with the continuous form because the usage and date values showed for all records not just the current? Did you find a workaround?

[10 Gauge]

I thought that you were not going to go with the continuous form because the usage and date values showed for all records not just the current? Did you find a workaround?

I decided to go with it just because it's the format that the guy wants for entering the new values. He told me he doesn't mind that it displays the same value for everything when he changes the value in a box. So I just decided to stick with it.

Another thought about that. Could I add two new fields in the Assets table for "UpdateUsage" and "UpdateUsageDate" ? Then I could bound the text boxes couldn't I? And wouldn't that also make it easier to run an append query on?

Just thinking out loud over here.....

[jzwp11]

If the customer is OK with it, then go for it.

[10 Gauge]

If the customer is OK with it, then go for it.

I added a few thoughts at the end of my last post, tell me what you think.

The SQL code line you gave me with VALUES, this is how VB wanted it formatted:

mySQL = mySQL & " VALUES(" & Me.AssetID & ", " & Me.tbUsage & ", #" & Me.tbUsageDate & "#)"

Does it still look ok? I tried firing it on a test update and it didn't append any data to my table.