Capturing button clicks

[just_some_guy]

I'm not sure what section to post this in, so I'll put it in "General. If this is incorrect, please let me know.

I have a form/table that tracks supply inventories. When the inventory is running low, the user clicks a button and it sends our supply guy an e-mail asking him to order it.

My boss wants to track what day particular supplies are ordered by tracking when the "order" button is clicked. Preferrably, he says, it would record the date of the click and the product name in a seperate table.

Any ideas on how I would go about doing this?

Thanks for any help!

 

[Trevor G]

Take a look at this which I created for someone a while ago, you can add to this by adding an extra field to maintain the product.

I have also created this code for you incase James, I created a table called tblFormatUpdated and added 2 fields Formupdated and ByUser.

Private Sub Command0_Click()
'Use current database
Dim db As DAO.Database
'Use the table = "tblFormUpdated"
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFormUpdated")
'Add a new record completing the FormUpdate field (this is a textbox with a date/time format)
'Add the user name to the ByUser Field
'Finally update the record
With rst
.AddNew
.Fields("FormUpdated").Value = Date
.Fields("ByUser").Value = Environ("username")
.Update
End With
End Sub

 

[just_some_guy]

Trevor G,

Thanks for the response, I've almost got the code doing what I need it to.

Here is the code after my modifications

Private Sub Command91_Click()
'Use current database
Dim db As DAO.Database
'Use the table = "tblFormUpdated"
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFormUpdated")
'Add a new record completing the FormUpdate field (this is a textbox with a date/time format)
'Add the user name to the ByUser Field
'Finally update the record
With rst
.AddNew
.Fields("FormUpdated").Value = Date
.Fields("Glass Cleaner").Value = Environ("Glass Cleaner")
.Update
End With

The last "field" line is variable. It could read "Glass Cleaner" or "Razor Blades", depending on the button. Again, that's working fine.

Where I'm running into a problem is that it is not pulling over the value for "Glass Cleaner." Preferrably, it would enter a 1 into the correct column on the table, but it could also read over whatever is in that field on the form.

Any ideas on that?

Thanks for the help.

 

[The_Doc_Man]

The "Environ" function reads stuff from your Windows environment. That would only work if you had an environmental variable defined for "GlassCleaner". And I don't think you can have a space in the name of an environmental value anyway.

It is not clear from your description what you were expecting to pull in for that entry. Can you elaborate a little?

 

[just_some_guy]

The_Doc_man,

Sorry, I'm horrible at clarity sometimes.

At any rate, here is what I have right now.

I have a table that tracks current inventory levels, with a new record for each date. There is a form in place that they key into, and it also shows their "Minimum Quantity." If the number of supplies on hands slips below the medium level, a message box pops up and instructs them to order, or tells them where they can find more.

A button to the side of each category allows the user to click that and send an e-mail to our supply guy requesting an order be placed.

I have a second table (that this code from my previous post feeds into[Thanks Trevor G for getting me that far!]) that captures the date a button was pushed. The table has the date field, and then a column for each item in the inventory. When they click the button, I need it to add a record for that date, and then put something in the item column showing that it was ordered on that day.

Is there a way to do this?

Thanks for your help.

 

[ghudson]

You need to have an Item field in your table. Then this line will update that field with the item that needs to be ordered instead of using this line >
.Fields("Glass Cleaner").Value = Environ("Glass Cleaner")

.Fields("Item").Value = txtItem

txtItem needs to be renamed to the name of the field in your form that displays the Item that needs to be ordered.

You can also add a Quanity field to capture the quanity requested to be ordered.

.Fields("Quanity").Value = txtQuanity

 

[just_some_guy]

ghudson,

Thanks for the help. I tried it out, and it still successfully creates the record with the date on it, but also still doesn't put in anything to the item column.

Here is what I changed the code to:

.Fields("Glass_cleaner").Value = txtglass_cleaner

If I'm understanding correctly .fields("glass_cleaner").value is referring to the column in the table I'm updating, while txtglass_cleaner refers to the form/table that this code is running from. Am I correct in that?

Again, thanks for all the help. I figure what ever I'm doing wrong at this point is an easy fix, I just need help figuring out what that is!

 

[Trevor G]

Try adding the .value after the txtGlass_Cleaner

I have just adjusted my code and it picks it up fine.

Private Sub Command0_Click()
'Use current database
Dim db As DAO.Database
'Use the table = "tblFormUpdated"
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFormUpdated")
'Add a new record completing the FormUpdate field (this is a textbox with a date/time format)
'Add the user name to the ByUser Field
'Finally update the record
With rst
.AddNew
.Fields("FormUpdated").Value = Date
.Fields("ByUser").Value = Environ("username")
.Fields("Glass_Cleaner").Value = txtGlassCleaner.Value

.Update
End With
End Sub

 

[just_some_guy]

Trevor G,

I'll give it a shot. Thanks very much for the help!

 

[ghudson]

ghudson,

Thanks for the help. I tried it out, and it still successfully creates the record with the date on it, but also still doesn't put in anything to the item column.

Here is what I changed the code to:

.Fields("Glass_cleaner").Value = txtglass_cleaner

If I'm understanding correctly .fields("glass_cleaner").value is referring to the column in the table I'm updating, while txtglass_cleaner refers to the form/table that this code is running from. Am I correct in that?

Again, thanks for all the help. I figure what ever I'm doing wrong at this point is an easy fix, I just need help figuring out what that is!

Is Glass Cleaners the only item that you need to order? If so then it would make sense to name your field specifically to "Glass_Cleaner". If you need to order various items then you need to be more generic and name the field something like "Items" in the table and the text box would need to be named "txtItems" in your form. I would avoid using special characters like the underscore _ when naming objects. User GlassCleaner instead of Glass_Cleaner.

To test what the value is in the text box when you are running your code you could use either the debugger or a message box.

Debug.Print "txtGlassCleaner = " & txtGlassCleaner 
MsgBox "txtGlassCleaner = " & txtGlassCleaner

HTH

 

[just_some_guy]

Ghudson,

Unfortunately it's not the only item. It's one of 15 or so, and the person who requested this wants each one to have it's own category.

I will go back and change Glass_cleaner to GlassCleaner, but can I ask (because I'm very new to Access), why avoid using special characters like the underscore in naming? Outside of it just being easier when typing it out and not having to remember to put it in?

Thanks for all the input. This site is a huge help, and everyone here has been super-patient with a newbie like me.

 

[ghudson]

You have a serious design flaw in your table structure if you have a separate field for each item that needs to be ordered. You need to have only one field named Items in your table. You can add another field to help identify the items like a Category field if that helps create groups for your items. You need to ensure your database tables are normalized and you would be breaking that rule if you structure your database with a new field for each different item that you need to track!

 

[ghudson]

Ghudson,

I will go back and change Glass_cleaner to GlassCleaner, but can I ask (because I'm very new to Access), why avoid using special characters like the underscore in naming? Outside of it just being easier when typing it out and not having to remember to put it in?

This should help... Error message when you use special characters in Access databases

Many developers follow something similar to Leszynski naming convention for naming their database objects.

 

[just_some_guy]

Trevor G and all,

I've put in the ".value" at the end of the line of code as recommended, and taken out the hyphens in the "GlassCleaner" names.

Here is what my code looks like now:

'Use current database
Dim db As DAO.Database
'Use the table = "tblFormUpdated"
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFormUpdated")
'Add a new record completing the FormUpdate field (this is a textbox with a date/time format)
'Add the user name to the ByUser Field
'Finally update the record
With rst
.AddNew
.Fields("FormUpdated").Value = Date
.Fields("GlassCleaner").Value = txtGlassCleaner.Value

.Update
End With

The problem I'm running into now is that I get a run-time '424' error: Object Required on the line

.Fields("GlassCleaner").Value = txtGlassCleaner.Value

Where am I going wrong?

 

[Trevor G]

Look at this link it may assist other than that you might be better of uploading the database (less data that is sensitive).

http://www.techonthenet.com/access/questions/obj_required.php