really silly filter problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sure I'm just tired, but I can't get this.

I have a table, "sale_information"
containing field "sale_dateID" as number
and "sale_current" as Yes/No

I have it joined to table "sale_dates"
containing "sale_date_ID" as number
and "sale_date" as date

I have a query filtering them to only return the current sale:

SELECT table_sale_information.sale_current, table_sale_dates.sale_date_id
FROM table_sale_dates INNER JOIN table_sale_information ON
table_sale_dates.sale_date_id = table_sale_information.sale_date
WHERE (((table_sale_information.sale_current)=True));

and now I have a form in which I enter sales of items, which I would like to
store the current date id in the field "clerking_date_ID", but I can't figure
out how to make the current date in the query become the default value for
the form field.

The current date's ID value right now is 1, but everything I try either
brings up a 0, or an error.

I have tried filtering the "clerking_date_id" field by the query, creating a
new text box containing the query result, creating a new combo containing the
result, but I can't get that 1 to show up.

What am I missing?
 
since the date id is the scond column in your combo, if you want it to
be the bound field, you need to set Bound Field --> 2


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Thank you for your response, But I would like to store the ID number, not the
actual date, and I want the ID number of the current sale to be the default
value.
 
DefaultValue, Find last record changed when form loads
---

Hi (what is your name?)

if you are storing the ID, you cannot set the control to a date. But
you can look up the ID that belongs to that date and make that the
default value when the form is loaded

'~~~~~~~~~~~~~
Private Sub Form_Load()
dim mDateID as long
mDateID = nz( _
dLookup("sale_dateID" _
,"sale_information" _
,"sale_current = true") _
,0)
if mDateID <> 0 then
me.controlname.DefaultValue = """" & mDateID & """"
end if
End Sub
'~~~~~~~~~~~~~

even though your field is a number, DefaultValue is a string

I have my doubts about the way you appear to be storing the default
information ... I am assuming that sale_current is false for every
record but one? Does this simply indicate the last record you created
or modified?

here is another suggestion:

put these 2 fields in every table and make sure they are also on your
form (I use the form footer and Lock the controls):

DateCreated, date, DefaultValue = Now()
DateModified, date – set on the form BeforeUpdate event

the best way to use the DateCreated field is to set a default value of
=Now()
in the table design.

For DateModified, use the Form BeforeUpdate event to set the value

me.DateModified = now()

then, since this information will be recorded...

'~~~~~~~~~~~~~
'find last record created or edited when form loads


Private Sub Form_Load()

'set up Error Handler
On Error GoTo Proc_Err

Dim mSomeID As Long _
, mDate1 As Date _
, mDate2 As Date

mDate1 = DMax("DateModified", "Tablename")
mDate2 = DMax("DateCreated", "Tablename")

If mDate2 > mDate1 Then
mSomeID = DLookup("SomeID" _
, "Tablename" _
, "DateCreated=#" & mDate2 & "#")
Else
mSomeID = DLookup("SomeID" _
, "Tablename" _
, "DateModified=#" & mDate1 & "#")
End If

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mSomeID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Proc_Exit:
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume

Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~

WHERE
SomeID is sale_dateID
Tablename is sale_dates



Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Thank you; response below.
if you are storing the ID, you cannot set the control to a date. But
you can look up the ID that belongs to that date and make that the
default value when the form is loaded

'~~~~~~~~~~~~~
Private Sub Form_Load()
dim mDateID as long
mDateID = nz( _
dLookup("sale_dateID" _
,"sale_information" _
,"sale_current = true") _
,0)
if mDateID <> 0 then
me.controlname.DefaultValue = """" & mDateID & """"
end if
End Sub
'~~~~~~~~~~~~~
Click to expand...


That is what I am looking for, as I am storing the ID number, not the date.
The date actually doesn't even factor, it is just an association to the ID
number.

I'll tell you more about my structure, to explain why what you suggest below
might not work.

I have a table "Sale Information". A sale is not a thing, it is an event.
Most sales are entered in the present, and archived when they are done, but
some are entered as future dates.

What I would like is to have one date in "sale Information" to be checked as
"current", so that is the global filter for all other forms and reports. If
a user wished to view or print historical data, they would go to "Open Sale"
form, and select which sale they would like to have as current.

Where this applies to my orignal question is, I would like the ID number for
the only date marked "current" to be the default value in any new records.

So, if "sale_dateID" = 1 is the only entry with "sale_current"=true, then
"1" wll be the default value in the "sale_date_ID" field on all new entries I
make into inventory ad well as any invoices generated.

Hopefully you get my drift, and if your code below can be modified to work
with this set-up, or if you have any suggestions re: structure, I would
appreciate you helping me with it.

Thanks,
Nancy.
 
Perhaps you should set up a Defaults table. I usually do this in my
databases. The only record in the Defaults table that is important is
the first one. Then you can have a field called sale_dateID in there --
when it changes, you just have one thing to change and don't have to
worry about modifying other records

to change it (assuming you are on the record you wish to make current
and you are in the code behind the form)

dim strSQL as string
strSQL = "UPDATE Defaults SET sale_dateID = " & me.sale_dateID
currentdb.execute strSQL
doEvents

to load it:
dim mDateID as long
mDateID = nz( _
dFirst("sale_dateID" _
,"Defaults"),0)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top