really silly filter problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

miss031 - 18 Mar 2007 06:14 GMT

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?

Reply to this Message

strive4peace - 18 Mar 2007 20:26 GMT

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
*

> I'm sure I'm just tired, but I can't get this.
>
[quoted text clipped - 26 lines]
>
> What am I missing?

Reply to this Message

miss031 - 18 Mar 2007 20:55 GMT

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.

> 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
[quoted text clipped - 39 lines]
> >
> > What am I missing?

Reply to this Message

strive4peace - 19 Mar 2007 03:52 GMT

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 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
[quoted text clipped - 43 lines]
>>>
>>> What am I missing?

Reply to this Message

miss031 - 19 Mar 2007 04:20 GMT

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
[quoted text clipped - 13 lines]
> End Sub
> '~~~~~~~~~~~~~

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.

> even though your field is a number, DefaultValue is a string
>
[quoted text clipped - 131 lines]
> >>>
> >>> What am I missing?

Reply to this Message

strive4peace - 19 Mar 2007 05:38 GMT

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
*

> Thank you; response below.
>
[quoted text clipped - 181 lines]
>>>>>
>>>>> What am I missing?

Reply to this Message