Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old October 18th, 2003, 09:29 AM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sal: The SQL code works great! However, one final detail before I can put this one to bed.... I need the "DailyID" results to appear in a text box on a data entry form. Having trouble getting the results ("DailyID") to appear in the form... am I placing the SQL in the wrong place? (I tried putting the query result "DailyID" value in as the Control source. Any thoughts??

Thanks!
Scott

Four more weeks before I sleep.......
Reply With Quote
  #12 (permalink)  
Old October 18th, 2003, 01:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Scott,

You can also get what you need using aggregate functions. Paste the following in your form's Current event. All the text boxes are bound. I'm not using any queries. Just a form and a table:

Private Sub Form_Current()

    Dim datCur As Date
    Dim datMax As Date
    Dim intDailyID As Integer

    datCur = Me!txtDateIn
    datMax = Nz(DMax("DateIn", "T1"), Date)

    If Me.NewRecord Then
        If datCur = datMax Then
            intDailyID = Nz(DMax("[DailyID]", "T1", _
                "[DateIn] = #" & Me!txtDateIn & "#"), 0) + 1
        Else
            intDailyID = 1
        End If

        Me!txtDailyID = intDailyID

    End If

End Sub


You can test this by playing with the system clock. Here's my CSV file output. The table fields are ID, DailyID, DateIn, TimeIn.

1,1,10/20/2003 0:00:00,10/20/2003 14:37:47
2,2,10/20/2003 0:00:00,10/20/2003 14:37:50
3,3,10/20/2003 0:00:00,10/20/2003 14:37:51
4,4,10/20/2003 0:00:00,10/20/2003 14:37:52
5,5,10/20/2003 0:00:00,10/20/2003 14:37:53
6,1,10/21/2003 0:00:00,10/21/2003 14:38:16
7,2,10/21/2003 0:00:00,10/21/2003 14:38:16
8,3,10/21/2003 0:00:00,10/21/2003 14:38:17
9,4,10/21/2003 0:00:00,10/21/2003 14:38:17
10,5,10/21/2003 0:00:00,10/21/2003 14:38:17
11,1,10/22/2003 0:00:00,10/22/2003 14:38:37
12,2,10/22/2003 0:00:00,10/22/2003 14:38:37
13,3,10/22/2003 0:00:00,10/22/2003 14:38:37
14,4,10/22/2003 0:00:00,10/22/2003 14:38:38
15,5,10/22/2003 0:00:00,10/22/2003 14:38:38

Regards,

Bob



Reply With Quote
  #13 (permalink)  
Old October 18th, 2003, 03:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Scott

And if you don't like Access's domain aggregate functions (used in the last post), you can easily convert them to SQL aggregate functions that return a scaler (single) value by creating a user-defined function (FMax) as follows:

Private Sub Form_Current()

    Dim datCur As Date
    Dim datMax As Date
    Dim intDailyID As Integer

    datCur = Me!txtDateIn
    datMax = Nz(FMax("SELECT MAX(DateIn) FROM T1"), Date)

    If Me.NewRecord Then
        If datCur = datMax Then
            intDailyID = Nz(FMax("SELECT MAX(DailyID) FROM T1 " & _
                                 "WHERE DateIn = #" & Me!txtDateIn & "#"), 0) + 1
        Else
            intDailyID = 1
        End If

        Me!txtDailyID = intDailyID

    End If

End Sub

Public Function FMax(strSQL As String) As Variant

    Dim rst As ADODB.Recordset
    Dim intCount As Integer

    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic

    If IsNull(rst(0)) Then
        FMax = Null
    Else
        FMax = rst(0)
    End If

    rst.Close
    Set rst = Nothing

End Function

Regards,

Bob

Reply With Quote
  #14 (permalink)  
Old October 18th, 2003, 03:01 PM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Bob: This is quite close to what I'm looking to be able to do (though Sal's reply was helpful too). Now for the 1,000th newbie question of the day.... could you please be "more specific" on where I get to my form's "Current event." Where do I insert the code? (BTW, I tested it in a beta table and it works perfectly!) I'd love to be able to have this in my form (which already exists...). I already have the DateIn and TimeIn fields in my form (and they work quite well...) but would like to "add" a text box which will display only the DailyID as the Sub formulates it. I see no reason for my users to need to view the primary key autonumber field on the data entry form.... I know it's there and how to use it in queries.... my users are even "newer" to Access than I and thus will not be writing their own queries.

Quote:
quote:
Paste the following in your form's Current event. All the text boxes are bound. I'm not using any queries. Just a form and a table:

Private Sub Form_Current()

    Dim datCur As Date
    Dim datMax As Date
    Dim intDailyID As Integer

    datCur = Me!txtDateIn
    datMax = Nz(DMax("DateIn", "T1"), Date)

    If Me.NewRecord Then
        If datCur = datMax Then
            intDailyID = Nz(DMax("[DailyID]", "T1", _
                "[DateIn] = #" & Me!txtDateIn & "#"), 0) + 1
        Else
            intDailyID = 1
        End If

        Me!txtDailyID = intDailyID

    End If
            
End Sub
Reply With Quote
  #15 (permalink)  
Old October 18th, 2003, 04:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Scott,

First, I assume you already did this, but I added a DailyID field to the base table. Looks like you guys were calculating it, but I suspect you'll want it as a persisted value.

Second, I have four text boxes on my form:

txtID (control source = ID)
txtDailyID (control source = DailyID)
txtDateIn (control source = DateIn)
txtTimeIn (control source = TimeIn)

The form is bound to table T1, which contains these 4 fields. DateIn default value = Date(), Time in default value = Now(), format = hh:nn:ss.

Third, to use the modules, open your form in design view and press F4 to open the form's properties dialog (or select View -> Properties). Click the Event tab in the Properties dialog. 'On Current' is the first event listed. Click in the text box next to 'On Current'. An drop down arrow and an ellipsis will appear at the far right of the textbox. Click the ellipsis. A small 'Choose Builder' dialog will appear. Select 'Code Builder'. The Visual Basic editor will open and you'll see a procedure body with Private Sub Form_Current() as the procedure header and End Sub as the footer. Just copy and paste the code I posted and paste it into the new procedure body (remove the duplicate procedure header or footer if you need to).

I took this route into the VB Editor to make sure that the words [Event Procedure] now appear in the textbox next to 'On Current' in the form Properties dialog. If they aren't there for some reason, select the drop down arrow next to 'On Current' to select them.

The On Current event fires a) when you open the form, b) each time you navigate to a new record. The code automatically increments the value in txtDailyID, which is saved to table T1 when you move to the next (or previous) record.

Regards,

Bob



Reply With Quote
  #16 (permalink)  
Old October 18th, 2003, 04:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Oh, hiding the autonumber textbox is fine.

Reply With Quote
  #17 (permalink)  
Old October 18th, 2003, 04:40 PM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wow, Bob! Thanks for the detailed reply, and for your patience! You're right, we probably will need to keep the value as a persisted value. I'll begin right now to try out your suggestion... I'll be back soon to let you know how it all works out!!

Thanks again!
Scott (a very FRUSTRATED guy in New York!)
Reply With Quote
  #18 (permalink)  
Old October 18th, 2003, 04:48 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Scott,
I am working on an easy solution for you. Without the need to learn how to code.
What exactly do youdo for a living, You do not work in It, Do you?

Just making sure we keep it simple for you.



Sal
Reply With Quote
  #19 (permalink)  
Old October 18th, 2003, 05:34 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Never mind, I read Bob's wrong. his will give you a permanent value to use forever.

Sal
Reply With Quote
  #20 (permalink)  
Old October 18th, 2003, 05:42 PM
Authorized User
 
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sal: Thanks for keeping it simple! hahaha

I actually work in a Document Production Center in a law firm. However, my normal duties involve managing people and work flow, NOT writing code! However, as I am the most experienced in the department with Access (scary thought!) I've been given the task of developing a database to track the documents ("jobs") that come into the center. We track the document number, client billing information, shift, date/time the document comes in for processing, the extent of revisions needed, document status (done, emailed, faxed, etc.) as well as the word processor and proofreader who work on the document and the time spent on the document. For years we've used a handwritten form, but with a new manager in the house (from an accounting background) we are being asked to provide statistics on usage, billing, work time v. downtime, peak periods/off-peak periods, etc.
The database is to be a tool to help us track the documents that come into the center, as well as work time.... I have my lovely autonumber as PK, but the "boss" wants these incrementing "job log #s" that re-start at "1" each new day.:(

So: I have zero experience in VBA, but with direction can at least put in the code... Query writing is not hard for me, but direct SQL work is not a strong point either...

So you know, the form we have so far is a "tabbed" form: the main tab is the "document info" area, with a tab for "operator" time and another tab for "proofreader" time. The tabs all reference my autonum field so that they will work with the main screen. I need to get rid of the autonum field on the main form and replace it with this infernal "job log number" field that restarts each successive day. Any and all help is appreciated! I've already learned quite a bit playing around with all of the suggestions I've received.

Forgive my verbosity!
Thanks in advance!
Scott
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
JMeter: Need help Urgently motionless BOOK: Expert One-on-One J2EE Design and Development 0 February 19th, 2008 12:16 PM
Urgently Need Help humayoo Beginning PHP 2 October 16th, 2007 02:39 PM
Need Help Urgently dpkbahuguna Beginning VB 6 2 August 10th, 2006 01:29 AM
urgently need help sumeghagupta .NET Web Services 1 October 5th, 2004 11:38 PM



All times are GMT -4. The time now is 08:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.