Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 16th, 2003, 05:12 AM
Authorized User
 
Join Date: Jul 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do I.....? One for the Experts!?

Hello, im pretty new to access programing and im trying to do the following....

I have a customer form, fname lname etc, on that form i have the calendar control and a list box..

what i want to be able to do is select a date from the calendar and it will automatically (or via "add" button) add the selected date into the listbox, and then click another date and that gets added to the listbox etc...
i would also like a remove button and a save button, the save button would add the mutlimple dates selected into a table field call "selecteddates".
I would also need the dates to remain in the listbox if the user had to edit it in the future.

I know im asking quiet alot but my company is despratley needing this function, ive been through some of the wrox books "begining access 2002 vba", and "begining visual basic 6" and i just cant get my head around how to do this.

any help on this matter would be so much appreciated!!!!

thanks

David

 
Old October 16th, 2003, 11:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi David,

This should get it:

1. I created a table with 2 fields called tblDates:

    ID (autonumber)
    TheDate (date/time)

2. Then, I created a query called qryDates that includes these two fields:

   SELECT tblDates.ID, tblDates.TheDate
   FROM tblDates;

3. Then, I added 4 controls on a form:

   a calendar control named oxcCalendar
   a listbox named lstDates
   a command button named cmdAddDate
   a command button named cmdDeleteDate

   oxcCalendar’s control source should be blank.

   Set the following properties for lstDates:

   Control source = blank (unbound)
   Row Source Type = Table/Query
   Row Source = qryDates
   Bound Column = 1
   Column Count = 2
   Column Widths = 0”;1”

4. Here is the click event of cmdAddDate

Private Sub cmdAddDate_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim datDateValue As Date

    Set db = CurrentDb

    strSQL = "SELECT TheDate FROM tblDates " _
        & "ORDER BY TheDate"

    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

    datDateValue = Me![ocxCalendar].Value

    With rst
        .AddNew
        !TheDate = datDateValue
        .Update
    End With

    lstDates.Requery

    rst.Close
    Set rst = Nothing

End Sub

6. Here is the click event of cmdDelete

Private Sub cmdDeleteDate_Click()

    Dim db As DAO.Database
    Dim strSQL As String
    Dim intID As Integer
    Dim strCriteria As String

    If IsNull(lstDates.ItemData(lstDates.ListIndex)) Then
        MsgBox "Choose a record to delete.", 48
        lstTest.SetFocus
    Else

        intID = lstDates.ItemData(lstDates.ListIndex)

        Set db = CurrentDb

        strSQL = "DELETE * FROM tblDates "
        strSQL = strSQL & "WHERE ID = " & intID

        x = MsgBox("Do you really want to delete record " _
            & Chr(10) & Chr(13) & intID, 36)
        If x = 6 Then
            db.Execute strSQL
        End If

        db.Close
        Set db = Nothing

        lstDates.Requery

    End If

End Sub


Dates selected on the calendar control are first added to tblDates. They then appear in the listbox after the listbox is requeried.

HTH,

Bob


 
Old October 16th, 2003, 11:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

The calendar control name should be ocxCalendar. I noticed a typo.

 
Old October 16th, 2003, 11:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Don't forget to set a reference to the DAO object library.

 
Old October 17th, 2003, 08:52 AM
Authorized User
 
Join Date: Jul 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob wow thanx for that!, but when you say a reference to the DAO object libary what do you mean? (sorry for being dumb)
The other table i have is called "newdiver" and has a field called "selectDates" which is used to connect/link (via relationship) to "tbldates" field "ID".

thanks Bob

 
Old October 17th, 2003, 09:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Bob Bedell
 Don't forget to set a reference to the DAO object library.
The other alternative is to re-write the code using ADODB instead of DAO. Then you don't have to worry about referencing the old DAO library.

Access 97 used the DAO library as the standard.

Access 2000, 2002 and 2003 use the ADODB object collections. ADODB is the default. If you want or need to use DAO you must reference the DAO library - it's not automatic.

Setting the DAO reference.

Open the VBA editor - either in a form or report or in a code module.
From the menu select Tools / References.
If the "Microsoft DAO 3.6 Object Library" (or some other version) is not checked, check it and click Ok.



Rand
 
Old October 17th, 2003, 10:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi David,

Here's the ADO version. It doesn't make any difference whether you ADO or DAO with aging Jet engine.


Private Sub cmdAddDate_Click()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim datDateValue As Date

    Set cnn = CurrentProject.Connection

    strSQL = "SELECT TheDate FROM tblDates " _
        & "ORDER BY TheDate"

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

    datDateValue = Me![ocxCalendar].Value

    With rst
        .AddNew
        !TheDate = datDateValue
        .Update
    End With

    lstDates.Requery

    rst.Close
    cnn.Close

    Set rst = Nothing
    Set cnn = Nothing

End Sub

Private Sub cmdDeleteDate_Click()

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Dim intID As Integer
    Dim strCriteria As String

    If IsNull(lstDates.ItemData(lstDates.ListIndex)) Then
        MsgBox "Choose a record to delete.", 48
        lstTest.SetFocus
    Else

        intID = lstDates.ItemData(lstDates.ListIndex)

        Set cnn = CurrentProject.Connection

        strSQL = "DELETE * FROM tblDates "
        strSQL = strSQL & "WHERE ID = " & intID

        x = MsgBox("Do you really want to delete record " _
            & Chr(10) & Chr(13) & intID, 36)
        If x = 6 Then
            cnn.Execute strSQL
        End If

        cnn.Close
        Set cnn = Nothing

        lstDates.Requery

    End If

End Sub

Regards,

Bob


 
Old October 17th, 2003, 10:20 AM
Authorized User
 
Join Date: Jul 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Great yeap that works, thanx rgerald but their is a problem,
when i select a customer and then add the dates then those dates will stay with ALL customers(i need them to be unique), I have a max no of 20 dates that can be selected to one customer, their will always be more than 1 date.

I have another table wich contains all the customer details and displays on the same form as the calendar, the table is called "New_Diver", so i need to link the two tables together (New_Diver to tbldates)

Thanks Guys

 
Old October 17th, 2003, 10:22 AM
Authorized User
 
Join Date: Jul 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry thanx bob (posted a reply the same time you posted)
could you help find a solution 4 this problem (linking the tables together)

 
Old October 17th, 2003, 03:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi David,

Give this a test drive.

******
Tables
******

tblDates
  - DateID (autonumber) PK
  - TheDate (dat/time)
  - DiverID (numer) FK

tblNewDiver
  - DiverID (autonumber) PK
  - FirstName (text)
  - LastName (text)

This is a 1:M relationship. Its a good practice to use the same name for the common field when creating a foreign key constraint (DiverID). DateID is optional. You could use a composite key if you want. Depends on your business rules.

*******
Queries
*******

qryDivers (record source for frmDivers)

SELECT DiverId, FirstName, LastName
FROM tblNewDiver;

qryDateList (record source for lstDates)

SELECT DateID, TheDate, DiverID
FROM tblDates
WHERE tblDates.DiverID=Forms!frmDivers!txtDiverID;

*****
Form
*****

Add the following controls to frmDivers and bind their control source property to the corresponding fields in frmDiver’s recordset (qryDivers):

txtDiverId (set Visible property to False if you want)
txtFirstName
txtLastName

lstDates is still unbound but you need to change its Record Source property to qryDateList

*******
Module
*******

I got rid of the recordset objects completely. You can just execute Insert and Delete queries to modify your tables. The Form’s Current event keeps the listbox in synch.

Option Compare Database

Private Sub cmdAddDate_Click()

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Dim datDateValue As String

    Set cnn = CurrentProject.Connection

    datDateValue = Me![ocxCalendar].Value

    strSQL = "INSERT INTO tblDates (TheDate, DiverID) " & _
                 "VALUES(# " & datDateValue & "# ," & Me!txtDiverId & ");"

    cnn.Execute strSQL

    lstDates.Requery

    cnn.Close

   Set cnn = Nothing

End Sub

Private Sub cmdDeleteDate_Click()

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Dim intID As Integer
    Dim strCriteria As String

    If IsNull(lstDates.ItemData(lstDates.ListIndex)) Then
        MsgBox "Choose a record to delete.", 48
        lstTest.SetFocus
    Else

        intID = lstDates.ItemData(lstDates.ListIndex)

        Set cnn = CurrentProject.Connection

        strSQL = "DELETE * FROM tblDates "
        strSQL = strSQL & "WHERE DateID = " & intID

        x = MsgBox("Do you really want to delete record " _
            & Chr(10) & Chr(13) & intID, 36)
        If x = 6 Then
            cnn.Execute strSQL
        End If

        cnn.Close
        Set cnn = Nothing

        lstDates.Requery

    End If

End Sub

Private Sub Form_Current()
    lstDates.Requery
End Sub

Regards,

Bob







Similar Threads
Thread Thread Starter Forum Replies Last Post
job for experts g_tufat Need help with your homework? 0 July 3rd, 2006 05:49 PM
Experts?? Problem w/ Webrequest MAtkins ASP.NET 1.0 and 1.1 Professional 2 April 9th, 2006 10:21 PM
Any xlink experts out there? mountainbiker XML 3 February 29th, 2004 01:49 PM
Any File (input #1) experts help? enterbase Access VBA 2 February 2nd, 2004 08:35 AM
Help, any javascript experts! tanmaylian Javascript 1 January 21st, 2004 04:59 AM





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