counting number of times record is accessed

[johnyjassi]

Hi Experts,

I have two queries here, if you could help me to solve

1) When the record is checked from the combo box, it should enter the number it is checked in a table. if it is checked again the number should add up.

2) Update the value from pending to processed on button click where record is current record chose in the combo box.

I will appreciate your help.
Thanks in advance!

 

[namliam]

Dont add numbers, you lose details, like when it was selected. I would insert the PK into a seperate table and run a count report of that table to count the number of selections.

 

[midmented]

I agree, keep a history in a different table.

2.) Include the "unique" key in the dropdown box and just hide it in the Column Widths column by making it's length 0". Use the unique key column value as a parameter in an update query to change the field value.

strSQl = "UPDATE tblTablename.Fieldname = 'Processed' WHERE tblTablename.Fieldname = '" & ComboName.Column(1) & "';"

 

[johnyjassi]

i did the same thing as you told me regarding update the column. its giving me error asying wrong expression 'combo1.column' and only one value is bound with that combo box that is id (PK). What should i do now.
Thanks!!

 

[namliam]

Combo boxes are 0 based... To fetch the first value Simply use ComboBoxName or ComboBoxName.Column(0).

Using Combo1 as the comboboxname is not really good, you should really use meaningfull names for you controls!
Also using the PK for users to select? Usually PKs are meaningless to users and/or should be hidden.

 

[johnyjassi]

Here is my code i wote on command click
DoCmd.RunSQL ("update callinfo set status = 'Closed' where callinfo.id = Combo1.column(0);")
It is showing the above error.

 

[midmented]

References

It sounds like you're not getting the value.
Do a msgbox stop until you see the value appear. Like:
What is the name of your combobox?

msgbox(Me!ComboNameHere)
stop

DoCmd.RunSQL ("update callinfo set status = 'Closed' where callinfo.id = Combo1.column(0);")

 

[midmented]

Also

DoCmd.RunSQL ("update callinfo set status = 'Closed' where callinfo.id = Combo1.column(0);")

You have to change that query:

DoCmd.RunSQL("Update callinfo set status = 'Closed' WHERE callinfo.id = " & Me.Combo1.column(0) & ";")

(As long as ID is a number and not a string)
If it is a string:
DoCmd.RunSQL("Update callinfo set status = 'Closed' WHERE callinfo.id = '" & Me.Combo1.column(0) & "';")


Dang, I should just give up for the day. Too many things going on. I had to edit this comment too many times.

I'm sorry.


Since the value of combobox is outside of the SQL statement, it needs quotes.

 

[johnyjassi]

looks like it was not recognizing the "combo1.column(0)", I put quotes around it and it is working thank you for your help guys! I apreciate that you spend your time on.