Combobox & update table with Now()

  • Thread starter Thread starter die_laffin via AccessMonster.com
  • Start date Start date
D

die_laffin via AccessMonster.com

Hi

I dont know whether what I am trying to do is a stupid idea anyway, but here
goes:

I have a table with 2 fields.

1stField is used as source for combobox.
2ndField I want to use to hold a date that indicates when the 1stField was
last selected in combobox

The combobox will then be sorted via 2ndField on form.

I guess I'm trying to use this as a MRU list

Any idea's on how to do this (I can't even work out how to pass Now() to the
2ndField!), or am I being stupid or trying to reinvent the wheel?

Any thoughts appreciated

Thanks

die_laffin
 
In the After Update event of the combo box:

Dim strSQL As String

strSQL = "UPDATE mytablename SET 2ndField = " & Now() & :WHERE 1stField =
""" & Me.MyCombo & """;"
CurrentDb.Execute(strSQL), dbFailOnError

Question is, do you really want to use Now? It returns date and time, where
Date() returns only the date.
 
Sure, try something like this in the AfterUpdate event of your combo box
(which I have called Combo0, but hopefully you have named somewhat better):

Private Sub Combo0_AfterUpdate()
Dim strSQL As String
strSQL = "UPDATE MRUTable SET MRUTable.[2ndField] = Now() " & _
"WHERE MRUTable.[1stField]= '" & Me.Combo0 & "' "
CurrentDb.Execute strSQL
Me.Combo0.Requery
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks for the suggestion Klatuu

I couldnt quite get that to work, but it did make me think about this
properly & I managed to get a Update Query to work for me.
The reason that I am using Now() is that I thought it would work more like a
MRU with the most recent at the top after my sort as I'm sorting by date &
time. Is that not correct?

Still not sure if I want to do this anyway. May change this so that it sorts
into the MOST USED, so that the most popular choices remain at the top & then
filter down to those rarely used.

Thanks again

die_laffin
In the After Update event of the combo box:

Dim strSQL As String

strSQL = "UPDATE mytablename SET 2ndField = " & Now() & :WHERE 1stField =
""" & Me.MyCombo & """;"
CurrentDb.Execute(strSQL), dbFailOnError

Question is, do you really want to use Now? It returns date and time, where
Date() returns only the date.
Hi
Click to expand...
[quoted text clipped - 19 lines]
die_laffin
Click to expand...
Click to expand...
 
It would have to be a syntax error not to work. I tested it using a table in
my system with different names. Anyway, you got it working. I'll bet if you
look at the SQL view of your update query, it will look a lot like this. The
Access query builder really just writes the SQL for you.

I like your most used idea. I don't know how you plan to do that, but my
first thought would be to have that second field be a number, and every time
it is chosen, add 1 to the number. You could still do that with an update
query.

die_laffin via AccessMonster.com said:
Thanks for the suggestion Klatuu

I couldnt quite get that to work, but it did make me think about this
properly & I managed to get a Update Query to work for me.
The reason that I am using Now() is that I thought it would work more like a
MRU with the most recent at the top after my sort as I'm sorting by date &
time. Is that not correct?

Still not sure if I want to do this anyway. May change this so that it sorts
into the MOST USED, so that the most popular choices remain at the top & then
filter down to those rarely used.

Thanks again

die_laffin
In the After Update event of the combo box:

Dim strSQL As String

strSQL = "UPDATE mytablename SET 2ndField = " & Now() & :WHERE 1stField =
""" & Me.MyCombo & """;"
CurrentDb.Execute(strSQL), dbFailOnError

Question is, do you really want to use Now? It returns date and time, where
Date() returns only the date.
Hi
Click to expand...
[quoted text clipped - 19 lines]
die_laffin
Click to expand...
Click to expand...
Click to expand...
 
It would have to be a syntax error not to work. I tested it using a table in
my system with different names. Anyway, you got it working. I'll bet if you
look at the SQL view of your update query, it will look a lot like this. The
Access query builder really just writes the SQL for you.
Click to expand...

Its when I was looking at your code that it reminded me of the SQL behind
another of my update queries. Thats what prompted me into looking at this
sensibly. You wouldnt believe some of the stupid things I was trying!
I like your most used idea. I don't know how you plan to do that, but my
first thought would be to have that second field be a number, and every time
it is chosen, add 1 to the number. You could still do that with an update
query.
Click to expand...

Thats what I was thinking of doing.

I'm also toying with the idea of adding this to more of my Comboboxes as it
seems to be a useful way of sorting the values.

Thanks again for your time & ideas

die_laffin
 
Back
Top