Subject:  Re: [access] How to REFRESH FORM Every 10 seconds



Maha,

You can use the Form Timer Event



Derrick



>>> "Maha Arupputhan Pappan" <mahap@p...> 12/06 3:37 AM >>>

Hi all,



I need to find a way to refresh a form every 10 seconds while it is 

active.



Thanks,

Maha

Subject:  Re: [access] Sending text messages from Access to Mobile

          Phones



Yes you can provided that the phone to be used accepts email messages.  If 

it does then you can use the docmd.sendobject.

Here's a sample using some string variables

DoCmd.SendObject , , , strTo, , , strSubject, strMessage, False



Check with your provider or the manufacture if your phone can accept 

emails.



Good luck,

Derrick



>>> "Brendan Moran" <brendan@b...> 12/11 12:37 AM >>>

My client has a repairs database in Access 97. What they would like the

database to do is that when the repairs have been made they would like 

to

hit a button in the database that would send a simple text message to 

the

customer saying the their bike is ready for collection.



Is it possible to send text messages from Access 97, 2k or XP to mobile

phones?



Regards



Brendan

Subject:  Re: [access] Grand Total in Report



If you have a calculated value in txtlumpsum that calculates a specific 

value within your detail section, you can just copy the txtlumpsum to your 

report footer and rename it.  When you try to do a sum of txtlumpsum, 

access can't find the field txtlumpsum that's why you get a dialog box.



Derrick,



>>> "Maha Arupputhan Pappan" <mahap@p...> 12/19 6:20 AM >>>

Hi all,



Good day.



I have problem calculating Grand Total in my Report. I have a calculated=20



field in the Detail section of my report. Let me call it TXTLUMPSUM. Now 

I

need to sum that field in my Group Footer section. I did this. I 

inserted

a field in the Footer section and called it TXTGRANDTOTAL and assigned 

its

data as =3DSUM(TXTLUMPSUM). When I preview report, it gives me a Dialog 

box

asking me to "Enter Parameter Value" for TXTGRANDTOTAL field.



How to over come this? Please guide.



Thanks,

Maha

Subject:  [access] Re: User Names:



Hello, sorry for interrupting your conversation but I do have some sample 

code that will pull the user name, computer name, and many other informatio

n about the computer that the user is using.



First create a Class Module and Name it "MyComputer" (Delete the Option 

Explicit section before pasting)

Then copy the entire code below into it.

Then in your form you can use after update event on your controls to pull 

the needed information.

example:  dim strUser as string

                  strUser =3D MyComputer.UserName



Here's Comes The Code!



Option Compare Database

Public myUser As String

Option Explicit



'*************************************************************************

'* INTERFACE                                                             *

'*************************************************************************



'Public collections

Public Drives As New Collection





'*************************************************************************

'* IMPLEMENTATION                                                        *

'*************************************************************************



'Windows structure declarations

Private Type SYSTEM_INFO

    dwOemID As Long

    dwPageSize As Long

    lpMinimumApplicationAddress As Long

    lpMaximumApplicationAddress As Long

    dwActiveProcessorMask As Long

    dwNumberOrfProcessors As Long

    dwProcessorType As Long

    dwAllocationGranularity As Long

    wProcessorLevel As Integer

    wProcessorRevision As Integer

End Type





Private Type OSVERSION_INFO

    dwOSVersionInfoSize As Long

    dwMajorVersion As Long

    dwMinorVersion As Long

    dwBuildNumber As Long

    dwPlatformId As Long

    szCSDVersion As String * 128

End Type



Private Type RECT

    x1 As Long

    y1 As Long

    x2 As Long

    y2 As Long

End Type



'Windows API function declarations

Private Declare Function SwapMouseButton Lib "user32" (ByVal bSwap As 

Long) As Long

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As 

Long) As Long

Private Declare Sub GetSystemInfo Lib "kernel32" (lpSystemInfo As 

SYSTEM_INFO)

Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" 

_

        (lpVersionInformation As OSVERSION_INFO) As Long

Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerN

ameA" _

        (ByVal sBuffer As String, lSize As Long) As Long

Private Declare Function GetUserName Lib "advapi32" Alias "GetUserNameA" _

        (ByVal sBuffer As String, lSize As Long) As Long

Private Declare Function timeGetTime Lib "winmm" () As Long

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Private Declare Function GetWindowRect Lib "user32" (ByVal hWnd As Long, 

rectangle As RECT) As Long

Private Declare Function GetLogicalDriveStrings Lib "kernel32" Alias 

"GetLogicalDriveStringsA" _

        (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

 



'Windows constant declarations

Private Const SM_SWAPBUTTON =3D 23

Private Const PROCESSOR_INTEL_386 =3D 386

Private Const PROCESSOR_INTEL_486 =3D 486

Private Const PROCESSOR_INTEL_PENTIUM =3D 586

Private Const PROCESSOR_MIPS_R4000 =3D 4000

Private Const PROCESSOR_ALPHA_21064 =3D 21064

Private Const PROCESSOR_ARCHITECTURE_INTEL =3D 0

Private Const PROCESSOR_ARCHITECTURE_MIPS =3D 1

Private Const PROCESSOR_ARCHITECTURE_ALPHA =3D 2

Private Const PROCESSOR_ARCHITECTURE_PPC =3D 3

Private Const PROCESSOR_ARCHITECTURE_UNKNOWN =3D &HFFFF

Private Const VER_PLATFORM_WIN32s =3D 0

Private Const VER_PLATFORM_WIN32_WINDOWS =3D 1

Private Const VER_PLATFORM_WIN32_NT =3D 2



'Private variable declarations

Private strAccessDir As String

Private strComputerName As String

Private strUserName As String

Private typSysInfo As SYSTEM_INFO

Private typOSVersion As OSVERSION_INFO

Private lngMemoryInstalled As Long

Private lngID As Long



Private Sub Class_Initialize()

'**************************************************************************

***

'* NAME:    CLASS_INITIALIZE

'* PURPOSE: To initialize variables when MyComputer object is instantiated

'* ACCEPTS: Nothing

'* RETURNS: Nothing

'* NOTES:   This procedure also populates the Disks collection

'**************************************************************************

***



Dim strDriveString As String

Dim intDriveLength As Integer

Dim strDrive() As String

Dim intDriveCount As Integer

Dim intPos As Integer

Dim intCounter As Integer

Dim lngBuffSize As Long

'Dim typDrive As Drive



'Get OS version information

typOSVersion.dwOSVersionInfoSize =3D Len(typOSVersion)

GetVersionEx typOSVersion



'Get system information (e.g. processor type)

GetSystemInfo typSysInfo



'Get Access directory

strAccessDir =3D SysCmd(acSysCmdAccessDir)



'Get computer name

strComputerName =3D Space$(255)

lngBuffSize =3D Len(strComputerName)

If GetComputerName(strComputerName, lngBuffSize) Then

    strComputerName =3D Left$(strComputerName, lngBuffSize)

End If



'Get user name

strUserName =3D Space$(255)

lngBuffSize =3D Len(strUserName)

If GetUserName(strUserName, lngBuffSize) Then

    strUserName =3D Left$(strUserName, lngBuffSize - 1)

    myUser =3D strUserName

End If





End Sub



Public Property Get AccessDir() As String

'**************************************************************************

***

'* NAME:    ACCESSDIR

'* PURPOSE: To allow the AccessDir property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the directory in which Access is installed

'* NOTES:

'**************************************************************************

***



AccessDir =3D strAccessDir



End Property



Public Property Get ButtonsSwapped() As Boolean

'**************************************************************************

***

'* NAME:    BUTTONSSWAPPED

'* PURPOSE: To allow the ButtonsSwapped property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: False if the mouse is configured for left-handed use

'*          True (non-zero) otherwise

'* NOTES:

'**************************************************************************

***



ButtonsSwapped =3D GetSystemMetrics(SM_SWAPBUTTON)



End Property



Public Property Let ButtonsSwapped(bSwap As Boolean)

'**************************************************************************

***

'* NAME:    BUTTONSSWAPPED

'* PURPOSE: To allow the ButtonsSwapped property to be set

'* ACCEPTS: True (non-zero) if the mouse is to be configured for left-hande

d use

'           False if the mouse is to be configured for (default) right-hand

ed use

'* RETURNS: Nothing

'* NOTES:

'**************************************************************************

***



Dim lngSwap As Long



lngSwap =3D CLng(bSwap)

Call SwapMouseButton(lngSwap)



End Property



Public Sub SwapButtons(bSwap As Boolean)

'**************************************************************************

***

'* NAME:    SWAPBUTTONS

'* PURPOSE: To expose the SwapButtons method

'* ACCEPTS: True (non-zero) if the mouse is to be configured for left-hande

d use

'           False if the mouse is to be configured for (default) right-hand

ed use

'* RETURNS: Nothing

'* NOTES:   This provides identical functionality to the ButtonsSwapped 

property

'**************************************************************************

***



SwapMouseButton bSwap



End Sub



Public Function SwapButtonCheck(bSwap As Boolean) As Boolean

'**************************************************************************

***

'* NAME:    SWAPBUTTONCHECK

'* PURPOSE: To allow the ButtonsSwapped property to be set and determine 

whether

'*          this results in a changed mouse configuration

'* ACCEPTS: True (non-zero) if the mouse is to be configured for left-hande

d use

'           False if the mouse is to be configured for (default) right-hand

ed use

'* RETURNS: True if the mouse configuration changes as a result of this 

call;

'*          False otherwise

'* NOTES:

'**************************************************************************

***



Dim bNewState As Boolean



bNewState =3D SwapMouseButton(bSwap)

If bNewState =3D bSwap Then

    SwapButtonCheck =3D False

Else

    SwapButtonCheck =3D True

End If



End Function



Public Property Get CPUArchitecture() As String

'**************************************************************************

***

'* NAME:    CPUARCHITECTURE

'* PURPOSE: To allow the CPUArchitecture property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the type of processor installed

'* NOTES:   FOr Win95, this will always be "Intel"

'**************************************************************************

***



Select Case typSysInfo.dwOemID

    Case PROCESSOR_ARCHITECTURE_INTEL

        CPUArchitecture =3D "Intel"

    Case PROCESSOR_ARCHITECTURE_MIPS

        CPUArchitecture =3D "MIPS"

    Case PROCESSOR_ARCHITECTURE_ALPHA

        CPUArchitecture =3D "Alpha"

    Case PROCESSOR_ARCHITECTURE_PPC

        CPUArchitecture =3D "PowerPC"

    Case PROCESSOR_ARCHITECTURE_UNKNOWN

        CPUArchitecture =3D "Unknown"

End Select



End Property



Public Property Get OSName() As String

'**************************************************************************

***

'* NAME:    OSNAME

'* PURPOSE: To allow the OSName property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the name of the operating system installed

'*          e.g. Windows NT 4.0 Build 1381

'*               ~~~~~~~~~~

'* NOTES:   Further information is in the OSVersion and OSBuild properties

'**************************************************************************

***



Select Case typOSVersion.dwPlatformId

    Case VER_PLATFORM_WIN32_WINDOWS

       OSName =3D "Windows 95"

    Case VER_PLATFORM_WIN32_NT

       OSName =3D "Windows NT"

End Select



End Property



Public Property Get OSVersion() As String

'**************************************************************************

***

'* NAME:    OSVERSION

'* PURPOSE: To allow the OSVersion property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the version of the operating system 

installed

'*          e.g. Windows NT 4.0 Build 1381

'*                          ~~~

'* NOTES:   Further information is in the OSVersion and OSBuild properties

'**************************************************************************

***



OSVersion =3D typOSVersion.dwMajorVersion & "." & typOSVersion.dwMinorVersi

on



End Property



Public Property Get OSBuild() As String

'**************************************************************************

***

'* NAME:    OSBUILD

'* PURPOSE: To allow the OSBuild property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the build of the operating system 

installed

'*          e.g. Windows NT 4.0 Build 1381

'*                                    ~~~~

'* NOTES:   Further information is in the OSVersion and OSBuild properties

'**************************************************************************

***



OSBuild =3D typOSVersion.dwBuildNumber And &HFFFF&



End Property



Public Property Get CPULevel() As String

'**************************************************************************

***

'* NAME:    CPULEVEL

'* PURPOSE: To allow the CPULevel property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the level of CPU installed

'* NOTES:   If the operating system is Win95, this value is found in the

'           dwProcessorType element returned by GetSystemInfo. If WinNT,

'           the value is in the wProcessorLevel element, and values 

returned

'           are dependent on processor architecture

'**************************************************************************

***



Select Case typOSVersion.dwPlatformId

    Case VER_PLATFORM_WIN32_WINDOWS

        Select Case typSysInfo.dwProcessorType

            Case PROCESSOR_INTEL_386

                CPULevel =3D "386"

            Case PROCESSOR_INTEL_486

                CPULevel =3D "486"

            Case PROCESSOR_INTEL_PENTIUM

                CPULevel =3D "Pentium"

        End Select

    Case VER_PLATFORM_WIN32_NT

        Select Case typSysInfo.dwOemID

            Case PROCESSOR_ARCHITECTURE_INTEL

                Select Case typSysInfo.wProcessorLevel

                    Case 3

                        CPULevel =3D "386"

                    Case 4

                        CPULevel =3D "486"

                    Case 5

                        CPULevel =3D "Pentium"

                End Select

            Case PROCESSOR_ARCHITECTURE_MIPS

                Select Case typSysInfo.wProcessorLevel

                    Case 4

                        CPULevel =3D "R4000"

                End Select

            Case PROCESSOR_ARCHITECTURE_ALPHA

                Select Case typSysInfo.wProcessorLevel

                    Case 21064

                        CPULevel =3D "21064"

                    Case 21066

                        CPULevel =3D "21066"

                    Case 21164

                        CPULevel =3D "21164"

                End Select

            Case PROCESSOR_ARCHITECTURE_PPC

                Select Case typSysInfo.wProcessorLevel

                    Case 1

                        CPULevel =3D "601"

                    Case 3

                        CPULevel =3D "603"

                    Case 4

                        CPULevel =3D "604"

                    Case 6

                        CPULevel =3D "603+"

                    Case 9

                        CPULevel =3D "604+"

                    Case 20

                        CPULevel =3D "620"

                End Select

            Case PROCESSOR_ARCHITECTURE_UNKNOWN

                CPULevel =3D "Unknown"

        End Select

End Select



End Property



Public Property Get ComputerName() As String

'**************************************************************************

***

'* NAME:    COMPUTERNAME

'* PURPOSE: To allow the ComputerName property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the name of the computer

'* NOTES:

'**************************************************************************

***



ComputerName =3D strComputerName



End Property



Public Property Get UserName() As String

'**************************************************************************

***

'* NAME:    USERNAME

'* PURPOSE: To allow the UserName property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the name of the currently logged-on user

'* NOTES:

'**************************************************************************

***



UserName =3D strUserName



End Property



Public Property Get WinStarted() As Date

'**************************************************************************

***

'* NAME:    WINSTARTED

'* PURPOSE: To allow the WinStarted property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A date indicating when Windows was last started

'* NOTES:   This value will not be accurate once the threshold of 

timeGetTime

'           has been exceeded (around 47 days)

'**************************************************************************

***



WinStarted =3D DateAdd("s", timeGetTime() / -1000, Now)



End Property



Public Property Get ScreenResolution() As String

'**************************************************************************

***

'* NAME:    SCREENRESOLUTION

'* PURPOSE: To allow the ScreenResolution property to be inspected

'* ACCEPTS: Nothing

'* RETURNS: A string indicating the current screen resolution (e.g. 

640x480)

'* NOTES:

'**************************************************************************

***



Dim rct As RECT

Dim hWnd As Long

Dim lngReturn As Long



hWnd =3D GetDesktopWindow()

lngReturn =3D GetWindowRect(hWnd, rct)



ScreenResolution =3D (rct.x2 - rct.x1) & "x" & (rct.y2 - rct.y1)





End Property



Public Property Get ID() As Long

'**************************************************************************

***

'* NAME:    ID

'* PURPOSE: To return the unique ID of this class instance

'* ACCEPTS: Nothing

'* RETURNS: A long representing a unique(-ish?) ID

'* NOTES:

'**************************************************************************

***

   

    ID =3D lngID



End Property





Good Luck And Spread The Word!

Derrick





>>> Kenneth Mungwira <kennethmungwira@y...> 12/19 9:08 AM >>>



 Please send me some sample coding?



Thank you-Kenneth Mungwira

  David Chapman <luckychap@b...> wrote: I don't believe access has 

an event tied to a table - so "short of limiting

the user write privelages" may not be possible. I will be looking for 

other

peoples answers here.

The solution I use is to split the database into two and link them,

(application and backend) using the wizard. Then secure the data component,



and pass a password in the link so that only the application can use it,

(and me with the password). In this way they can only change the data by

using the application. My log file entries are generated by events on 

forms

in the application, and note the network UserID of the user - in our

organization their machine IDs are mystical. In the backend DB (the data) 

I

have one start-up form which displays for 10 secs, tells them they do not

have access, then quits.



If any of this helps I will be happy to send sample code, but the key is 

the

users requirements, and if they demand access to the tables then you have 

a

problem.



----- Original Message -----

From: "KennethMungwira"

To: "Access"

Sent: Tuesday, December 18, 2001 8:15 PM

Subject: [access] User Names:





> Dear Sir or Madam,

>

> I have created a field on one of my tables that records MachineIDNames

> when the user makes changes to record through a form. Unfortunately, I

> have more and more people with write access to the database and they are

> now inputting information directly into the database(bypassing the

> forms). Would you have any surggestions on how to keep a record of these

> users, short of limiting the user "write" privelages.

>

> Q: Can I have a seperate table that only keeps track of all users who 

log

> into the database-Write or read access















---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday 

gifts!

--0-986422981-1008779971=:28675

Content-Type: text/plain; charset=us-ascii





I have users.id(MachineID's) which is enough information to find out the user, but if the user bypasses the forms and goes straight
to the tables then I have no way of knowing who is entering information into the tables. do you have any other suggestions? 

 







---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday gifts!

Subject:  [access] Re: Concealed



Try this while I look for more options,



1. Startup Options:  Now you can lock down your database so that they 

cannot either go behind the form or see the database windows. 

2. Hide Tables/Queries/Forms/Moduels:  Also, you can hide all of your 

tables.



Derrick,



>>> Kenneth Mungwira <kennethmungwira@y...> 12/19 10:39 AM >>>



I have users.id(MachineID's) which is enough information to find out the 

user, but if the user bypasses the forms and goes straight to the tables 

then I have no way of knowing who is entering information into the tables. 

do you have any other suggestions?









---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday 

gifts!

Subject:  [access] Re: Concealed



Here's another affective way to hide tables



If you want to hide a table in the database windows so that other users 

can't select it, preface the table's name with 'Usys'. For example, if 

your table's name is employee, change it to UsysEmployee. This makes your 

table into a system object which access does not display. To see this 

table later, choose view, options, show system objects.



Derrick,



>>> Derrick Flores 12/19 11:04 AM >>>

Try this while I look for more options,



1. Startup Options:  Now you can lock down your database so that they 

cannot either go behind the form or see the database windows. 

2. Hide Tables/Queries/Forms/Moduels:  Also, you can hide all of your 

tables.



Derrick,



>>> Kenneth Mungwira <kennethmungwira@y...> 12/19 10:39 AM >>>



I have users.id(MachineID's) which is enough information to find out the 

user, but if the user bypasses the forms and goes straight to the tables 

then I have no way of knowing who is entering information into the tables. 

do you have any other suggestions?









---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday 

gifts!

--0-1940236024-1008783330=:16694

Content-Type: text/plain; charset=us-ascii





 Thank you that works!!!

  Derrick Flores <Derrick_Flores@s...> wrote: Subject: [access] Re: Concealed



Here's another affective way to hide tables



If you want to hide a table in the database windows so that other users can't select it, preface the table's name with 'Usys'. For
example, if your table's name is employee, change it to UsysEmployee. This makes your table into a system object which access does
not display. To see this table later, choose view, options, show system objects.



Derrick,



>>> Derrick Flores 12/19 11:04 AM >>>

Try this while I look for more options,



1. Startup Options: Now you can lock down your database so that they cannot either go behind the form or see the database windows. 

2. Hide Tables/Queries/Forms/Moduels: Also, you can hide all of your tables.



Derrick,



>>> Kenneth Mungwira 12/19 10:39 AM >>>



I have users.id(MachineID's) which is enough information to find out the user, but if the user bypasses the forms and goes straight
to the tables then I have no way of knowing who is entering information into the tables. do you have any other suggestions? 









---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday gifts!

















---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday gifts!

Great idea that's the first I every heard something like that.  I suggest

you just lock the tables by using a module. 



Robert L. Rogers

Rexel Branch Electric

Application Developer

xxx-xxx-xxxx





-----Original Message-----

From: Derrick Flores [mailto:Derrick_Flores@s...]

Sent: Wednesday, December 19, 2001 12:28 PM

To: Access

Subject: [access] Concealed





Subject:  [access] Re: Concealed



Here's another affective way to hide tables



If you want to hide a table in the database windows so that other users

can't select it, preface the table's name with 'Usys'. For example, if your

table's name is employee, change it to UsysEmployee. This makes your table

into a system object which access does not display. To see this table later,

choose view, options, show system objects.



Derrick,



>>> Derrick Flores 12/19 11:04 AM >>>

Try this while I look for more options,



1. Startup Options:  Now you can lock down your database so that they cannot

either go behind the form or see the database windows.  

2. Hide Tables/Queries/Forms/Moduels:  Also, you can hide all of your

tables.



Derrick,



>>> Kenneth Mungwira <kennethmungwira@y...> 12/19 10:39 AM >>>



I have users.id(MachineID's) which is enough information to find out the

user, but if the user bypasses the forms and goes straight to the tables

then I have no way of knowing who is entering information into the tables.

do you have any other suggestions? 

 







---------------------------------

Do You Yahoo!?

Check out Yahoo! Shopping and Yahoo! Auctionsfor all of your holiday gifts!



















****************************************************************************

******************************************************

This e-mail and any files transmitted with it are confidential and intended

solely for the use of the individual or entity to whom they are addressed.

If you have received this e-mail in error please notify the sender and

delete/destroy any copies.

****************************************************************************

******************************************************

Subject:  Re: [access] Passing values into Query from Form



First create a form (name it frmCalendar) with the Calendar ActiveX 

Control only.



Then go into the form's Class Module and delect the Option Compare 

Database section then just copy this code below



Option Compare Database

Private mctlDate As Control

Option Explicit



Public Property Set DateControl(ByVal ctlDate As Control)

   

    Set mctlDate =3D ctlDate



End Property



Public Property Let InitialDate(datD As Date)

   

    Me.ctlCalendar.Value =3D datD



End Property



Private Sub SetAndClose()

   

    If mctlDate Is Nothing Then

        MsgBox "The DateControl property has not been set", vbInformation, 

"Calendar Form Error"

    Else

        mctlDate =3D ctlCalendar.Value

    End If

   

    DoCmd.Close



End Sub



Private Sub ctlCalendar_Click()

   

    SetAndClose



End Sub



Private Sub ctlcalendar_KeyUp(KeyCode As Integer, ByVal Shift As Integer)

   

    Select Case KeyCode

    Case vbKeyReturn

        SetAndClose

    Case vbKeyEscape

        DoCmd.Close

    End Select

   

End Sub



Once you finished with the frmCalendar then create a command button on the 

form that will need to input the date and copy the code below in the Click 

Event of the command button



Dim frmCal As Form

Dim ctlD As Control



Set ctlD =3D Me!txtStartDate

DoCmd.OpenForm "frmCalendar", , , , , acHidden

Set frmCal =3D Forms("frmCalendar")

With frmCal

    Set .DateControl =3D ctlD

    .InitialDate =3D ctlD.Value

    .Visible =3D True

End With



Copy this code for any other place that needs to reference the frmCalendar



Good luck,

Derrick



>>> "Maha Arupputhan Pappan" <mahap@p...> 12/20 7:57 AM >>>

Hi, I have a form with Calendar ActiveX Control and a Command Button. I

wish to allow my users to click a date from the Calendar Control and 

once

the user click the Command Button, it should send the value from the

Calendar and run the query based on the Date value selected in the

Calendar Control.



How can I do that?



Thanks,

Maha

Subject:  Re: [access] Passing values into Query from Form



Oh, I forgot one more thing.

You need to initialize the txtStartDate when you load your form.



Me.txtStartDate.Value =3D Date



Derrick



>>> "Maha Arupputhan Pappan" <mahap@p...> 12/20 7:57 AM >>>

Hi, I have a form with Calendar ActiveX Control and a Command Button. I

wish to allow my users to click a date from the Calendar Control and 

once

the user click the Command Button, it should send the value from the

Calendar and run the query based on the Date value selected in the

Calendar Control.



How can I do that?



Thanks,

Maha

Subject:  Re: [access] looping thru records to print report...



If you are using a report that has no RecordSource and attaching 7 

Subreports that is not bound to the main report you will not be able to 

group or sort by the main report since the subreports are not bound to the 

main report.  What I would do is, place a underlying query to the 

RecordSource in you main report, bound your 7 subreports, and then group 

and sort you main report. Make sure that you pass parameters to the 

underlying RecordSource of your main report.  This way you will force the 

grouping for your main report and all of the 7 subreports.



Good luck,

Derrick



>>> "Darin Wray" <darin@r...> 12/20 9:35 PM >>>

Hey guys...need some direction on how to code this scenario.



I have an unbound report that has 7 subreports in it.  Each subreport's

datasource is a stored proc with date and salesrep parameters.  These

parameters are passed based on fields that are entered in a form.  The

report works great for one rep at a time...I couldn't get it to sort/group



correctly on a report for ALL sales reps, tho.  Perhaps there is 

something

I'm missing...anyways, I thought another alternative would be to program=20



some kind of For Next statement to loop thru each employee, and print

their very own sales report.



Here's what I'm thinking...in english



Probably have to pull a recordset down that has the salesreps names...

for each salesrep where salesrep is value for a stored proc parameter

print salesreport where begin date and end date are stored proc parameter.



Can someone give me a rough example on how to code this in a command

button?



Thanks,

Darin

Subject:  Re: [access] Parameter expected when I try to run a query



Try,



dim rs as recordset

dim db as database

dim str as string



dim lastnum as integer



' Create a recordset that will sort (ascending) by EngItemNo

str =3D "SELECT * FROM TableName ORDER BY EngItemNo"



set db =3D currentdb()

set rs =3D db.OpenRecordset(str)



' The last record in the recordset will have the last (Max) EngItemNo 

Entered

rs.movelast

lastnum =3D rs("EngItemNo")



rs.close

db.close



docmd.gotorecord,,acnewrec



Me.EnqItemNo =3D lastnum + 1







Good Luck,

Derrick













>>> "Antony Peel" <Antony.Peel@m...> 12/21 11:57 AM >>>

I have written a query that gets the maximum item line number for an

enquiry.  I am trying to create a record set so that I can increment 

the

last item line number of the enquiry when I add a new record.



I keep getting a message that the query needs a parameter.  The query 

runs

OK and returns the correct result, but I can't get it to work with the

code below.



Private Sub AddEnqLineBut_Click()

On Error GoTo Err_AddEnqLineBut_Click

       

    Dim rs As Recordset

   

    Dim lastnum As Integer

    Dim newNum As Integer

       

    Set rs =3D CurrentDb.OpenRecordset("MaxItemQuery")

    'open query to find last line number for this enq num

       

    lastnum =3D rs!MaxOfEnqItemNo

    ' Assign last number to variable

   

    newNum =3D lastnum + 1

    ' Create new number for new record

   

    DoCmd.GoToRecord , , acNewRec

    Me.EnqItemNo =3D newNum



Exit_AddEnqLineBut_Click:

    Exit Sub



Err_AddEnqLineBut_Click:

    MsgBox Err.Description

    Resume Exit_AddEnqLineBut_Click

Hmm...I tried this, but couldn't make it work.  Each subreport is based on a

stored proc..and each subreport passes parameters to the sp to get the data

it needs, based on dates in a form.  I then tried binding the main report to

a sales rep table...and, did the sorting and grouping.  The report printed

like so...each rep, regardless of whether they really sold anything or not,

looked like they sold everything that everyone else did....the report for

each salesrep was identical.  I took the sorting and grouping off the main

report, and nothing changed...looked exactly the same.  For some reason, the

subreports weren't even acknowledging the recordsource and sort/group on the

main report....I messed with this for hours, and gave up...which is why my

next thought was to work out something in  vba code.



Darin

----- Original Message -----

From: "Derrick Flores" <Derrick_Flores@s...>

To: "Access" <access@p...>

Sent: Friday, December 21, 2001 8:49 AM

Subject: [access] Concealed





Subject:  Re: [access] looping thru records to print report...



If you are using a report that has no RecordSource and attaching 7

Subreports that is not bound to the main report you will not be able to

group or sort by the main report since the subreports are not bound to the

main report.  What I would do is, place a underlying query to the

RecordSource in you main report, bound your 7 subreports, and then group and

sort you main report. Make sure that you pass parameters to the underlying

RecordSource of your main report.  This way you will force the grouping for

your main report and all of the 7 subreports.



Good luck,

Derrick



>>> "Darin Wray" <darin@r...> 12/20 9:35 PM >>>

Hey guys...need some direction on how to code this scenario.



I have an unbound report that has 7 subreports in it.  Each subreport's

datasource is a stored proc with date and salesrep parameters.  These

parameters are passed based on fields that are entered in a form.  The

report works great for one rep at a time...I couldn't get it to sort/group

correctly on a report for ALL sales reps, tho.  Perhaps there is something

I'm missing...anyways, I thought another alternative would be to program

some kind of For Next statement to loop thru each employee, and print

their very own sales report.



Here's what I'm thinking...in english



Probably have to pull a recordset down that has the salesreps names...

for each salesrep where salesrep is value for a stored proc parameter

print salesreport where begin date and end date are stored proc parameter.



Can someone give me a rough example on how to code this in a command

button?



Thanks,

Darin

Subject:  Re: [access] Pass data in one field as a parameter in a

          query



Here's what you can do,

1. if your fields (controls) are not combo boxes, Right click on the text 

box > Change To > Combo Box



2. Again, if your fields (controls) are not combo boxes, create your 

RowSource for each combo boxes

   ex: cboMake RowSource =3D "Select VehMake From TableName"

         cboModel's RowSource =3D "Select VehModel, VehMake From TableName 

Where VehMake =3D Form!FormName!VehMake "

 

  Hint: In the criteria for the VehMake, use the Build tool to reference 

the text box on your form



3. Add some code to the After Update Event for you VehMake combo box

  ex:  If me!VehMake <> "" then

                me!VehModel.Requery

        end if



Good Luck,

Derrick





>>> "Noel Wilson" <nwilson4@t...> 12/21 3:31 PM >>>

I have a form that has two controls, vehicle make and vehicle model.  

The

source for both controls is a lookup list on table, you guessed it, tbl

makes and model  that has three fields, ID, Vehicle make and Vehicle

model.  I want to limit the list of vehicle models to those that 

coincide

with a particular make, IE  chevrolete in make field limits model 

choices

to corvette, astro, ect.  How do I do this.  



It seems that I could make the model field's source a query but how do 

I

pass the previous fields data as a parameter?  Very new at coding, so

write slowly K?



-N-

Subject:  [access] Re: Concealed



I believe the key to this, is to have a way of bind these reports together 

by a unique (ID) field(s).

Let me know 2 Things.

1. Are your table normalized? (Primary key, Foreign key)

2. Are your keys present in each reports? (Sales Rep ID)

3. Are are you using the Link Child Fields and Link Master Fields?



Good luck,

Derrick



>>> "Darin Wray" <darin@r...> 12/21 9:08 AM >>>

Hmm...I tried this, but couldn't make it work.  Each subreport is based on 

a

stored proc..and each subreport passes parameters to the sp to get the 

data

it needs, based on dates in a form.  I then tried binding the main report 

to

a sales rep table...and, did the sorting and grouping.  The report printed

like so...each rep, regardless of whether they really sold anything or 

not,

looked like they sold everything that everyone else did....the report for

each salesrep was identical.  I took the sorting and grouping off the main

report, and nothing changed...looked exactly the same.  For some reason, 

the

subreports weren't even acknowledging the recordsource and sort/group on 

the

main report....I messed with this for hours, and gave up...which is why my

next thought was to work out something in  vba code.



Darin

----- Original Message -----

From: "Derrick Flores" <Derrick_Flores@s...>

To: "Access" <access@p...>

Sent: Friday, December 21, 2001 8:49 AM

Subject: [access] Concealed





Subject:  Re: [access] looping thru records to print report...



If you are using a report that has no RecordSource and attaching 7

Subreports that is not bound to the main report you will not be able to

group or sort by the main report since the subreports are not bound to the

main report.  What I would do is, place a underlying query to the

RecordSource in you main report, bound your 7 subreports, and then group 

and

sort you main report. Make sure that you pass parameters to the underlying

RecordSource of your main report.  This way you will force the grouping 

for

your main report and all of the 7 subreports.



Good luck,

Derrick



>>> "Darin Wray" <darin@r...> 12/20 9:35 PM >>>

Hey guys...need some direction on how to code this scenario.



I have an unbound report that has 7 subreports in it.  Each subreport's

datasource is a stored proc with date and salesrep parameters.  These

parameters are passed based on fields that are entered in a form.  The

report works great for one rep at a time...I couldn't get it to sort/group

correctly on a report for ALL sales reps, tho.  Perhaps there is something

I'm missing...anyways, I thought another alternative would be to program

some kind of For Next statement to loop thru each employee, and print

their very own sales report.



Here's what I'm thinking...in english



Probably have to pull a recordset down that has the salesreps names...

for each salesrep where salesrep is value for a stored proc parameter

print salesreport where begin date and end date are stored proc parameter.



Can someone give me a rough example on how to code this in a command

button?



Thanks,

Darin

Subject:  [access] Re: Sort numbers



You can also use:



For set values "IC-"

CInt(Right([field1],Len([field1])-3))



or use the Instr to find a value "-" which is better!

CInt(Right([field],Len([field])-InStr([field],"-")))



Derrick



>>> "Clive Astley" <clive.astley@k...> 12/21 7:23 PM 

>>>

Hello Kenneth,



As a mere amateur I have some reservation about putting forward my 

simple

solution on a network of real experts (to whom I am always grateful for

the advice I often receive).  However, perhaps just putting an extra

calculated field in your query and sorting on that will suffice? The

calculated field I tried is Expr2: CInt(Mid([field1],4)) where field 

one

contains all your numbers IC-1 IC-2 IC-3 etc.



Hope this helps. Best wishes,

Clive







> Dear Sir or Madam,

>

> In a table I have a set of number which I am trying to sort(Asending)

>

> IE: IC-1,IC-2,IC-3,....IC-129

>

> These numbers have been moved and I would like to make access disreguard



> the "IC-" and only look at the number at the end.

>

> Can this be do?

> On design a query it does not let me specify what to leave out, only

> choose the field, and the manner of the sort.

>

> Please help