Results 1 to 15 of 15

Thread: Make Table (97)

  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make Table (97)

    Hi, I have a table with 16 names of departments in it. For each department i want another table to hold departmental info. Each of these tables will have the same structure.

    Q: Is there a quick way of making these tables, or do i have to do the <copy - structure only - rename> method 16 times?

    I dont think i can use the make table Query, but could i do a for next loop in VB?

    Thanks...
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table (97)

    Creating 16 tables with identical structure is not a very efficient design. I would prefer to create one table, with an extra field to identify the department, say DepartmentID (linked to the table with the names of the department). You can use a query to select the info for a single department.

    If you really want to create 16 tables, pasting structure only is probably just as fast as writing VBA code, since it seems to be a one-time operation. If you would have to do this repeatedly, code would be more efficient.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    Thanks Hans, I have restructed to the more efficient design of 1 table - [img]/forums/images/smilies/smile.gif[/img]

    a couple of questions ...

    1st...

    I have constantly been annoyed that my mouse scroll wheel doesnt scroll in the VB editor code window... is there a hidden option to turn this on? I have looked but no joy yet...

    2nd...

    when i do things like this

    DoCmd.OpenForm FormName:="Data Input", datamode:=acFormAdd

    its fine, but why when i use the brackets eg

    DoCmd.OpenForm ("Data Input", ,acFormAdd)

    etc, does access ask for an = something? i presume it is like a msgbox has to have a variable, but could u do a brief explaination? I am just confuddled about this, because it wold be much simpler to use the brackets notation rather than specify parameters constantly [img]/forums/images/smilies/smile.gif[/img]

    cheers,
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table (97)

    1. About the mouse wheel in the VBA editor, see the recent thread <post#=324669>post 324669</post#> in the VBA forum. The mouse wheel doesn't work in the VBA editor for me too, but it seems that getting the right mouse drivers will solve this.

    2. When you define a procedure or function, you use parentheses around the arguments:

    Sub MyProcedure(Arg1 As Long, Arg2 As String)

    Function MyFunc(Arg1 As Long, Arg2 As String) As Boolean

    But when you call a procedure, you don't use brackets:

    MyProcedure Arg1:=37, Arg2:="Lounge"

    or

    MyProcedure 37, "Lounge"

    When assigning the result of a function, you must use brackets:

    Dim MyBool As Boolean
    MyBool = MyFunc(37, "Lounge")

    In your case, you could use

    DoCmd.OpenForm "Data Input", , , , acFormAdd

    Note: since DataMode is the 5th argument of OpenForm, you must put 4 commas before it if you don't want to use named arguments.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    Ahhhhhaaaa!

    Thanks, its the brackets - i see [img]/forums/images/smilies/smile.gif[/img]

    another Q, hope im not tiring u...

    when doing a report, how do i reference a lable, lblTitle, and change the caption ?

    lblTitle.caption = strTitle doesnt seem to work.

    regards
    Thanks,

    pmatz

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table (97)

    Your code looks OK, but you can only use it in the On Print or On Format event of one of the sections of the report, or in the On Open event of the report. You can't change the caption of a label in a report that is already open. Where did you try to use this code?

  7. #7
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    I am trying to do it from the VB of a form's command button.

    strTable = lstDep.Column(1)

    DoCmd.OpenReport reportname:="viewdoc", view:=acViewPreview
    [Reports]![viewdoc].lbltitle.Caption = strTable

    or something like that...

    perhaps i should make the title a field from a query that would automatically be the correct department?
    Thanks,

    pmatz

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table (97)

    You can't do this from the form, after opening the report, but you could do it in the On Open event of the report:

    Private Sub Report_Open(Cancel As Integer)
    Me.lblTitle.Caption = Forms!frmSomething!lstDep.Column(1)
    End Sub

    If the report can be opened by itself, you may want to test if the form is open.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    Cool,

    out of interest, how do i check if a form is open...?

    im trying things like...

    if forms!input.show = true

    but that is obviously wrong!
    Thanks,

    pmatz

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table (97)

    This function from the Northwind demo database returns True if the specified form is open in datasheet or form view, False otherwise:

    Function IsFormLoaded(ByVal strFormName As String) As Integer
    Const conObjStateClosed = 0
    Const conDesignView = 0
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
    If Forms(strFormName).CurrentView <> conDesignView Then
    IsFormLoaded = True
    End If
    End If
    End Function

    Use like this:

    If IsFormLoaded("Input") Then
    ...
    End If

  11. #11
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    Thanx Hans, that works a treat [img]/forums/images/smilies/smile.gif[/img]

    my form has default view continous forms so that i can display records brought up from a query as a read only list to be viewed.

    I notice there are Can Grow and Can Shrink properties for the parts of the form...

    can i use these to enable the form to resize itself depending on the amount of records shown?, say if the number of records is smaller than 10 then the form would shrink to fit, else would reach a threshold and then the scroll bar could be used?

    also i notice that when the scroll bar is used with the mouse it scrolls down, but then wont scroll back up - i have to use the up arrow on the scroll bar to get the forms back to the top! is this a quirk?

    thanks again [img]/forums/images/smilies/smile.gif[/img]
    Thanks,

    pmatz

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Table (97)

    The CanGrow and CanShrink properties are mostly used in reports. In forms, they only work when the form is printed (or print previewed), not in form view or datasheet view. I don't know of a good way to do what you want.

    It is possible that the "thumb" of the vertical scroll bar is not completely accurate; this also occurs in Access 2002. Not much you can do about it, I'm afraid...

  13. #13
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    No problem, I am just curious! Thanks anyway Hans.

    I have much to learn

    take it easy
    Thanks,

    pmatz

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Make Table (97)

    If you want to resize form that displays records in Continuous Forms view, you will probably need to use an unbound main form with a subform control to display the records. Normally when resizing form, you would use a popup form, opened in a restored window, where the Detail section and/or form Header and Footer sections are resized dynamically based on some condition or criteria. This won't work when the form is opened in Continuous Forms view because the Detail section is repeated for each record, so adjusting its size would just make each record taller.

    If interested, the attached demo database demonstrates how this can be done using a subform. The test form (Form1) consists of a Detail section with a subform control (Subform1) that displays records in continuous view. The main form footer has a combobox that lists letters A to Z (plus "*" for all records). When a letter is selected, the subform is requeried to display only those records where CustomerID begins with selected letter (when form opens, all records are displayed). If less than 10 records, form is resized accordingly (if no records, one blank record is displayed). Sample code:

    Private Sub ResizeForm()
    On Error GoTo Err_Handler

    Dim strMsg As String
    Dim lngCount As Long
    Dim lngScrollbarHt As Long
    Dim rst As DAO.Recordset

    lngScrollbarHt = GetSystemMetrics(SM_CXVSCROLL) * TwipsPerPixelY

    Const MaxRecs = 10
    ' Subform detail section ht (single record) in twips:
    Const SubfrmDetailHt = 0.2083 * 1440
    ' Subform header section ht in twips:
    Const SubfrmHeaderHt = 0.25 * 1440
    ' Subform control max ht in twips:
    Const SubfrmMaxHt = (MaxRecs * SubfrmDetailHt) + SubfrmHeaderHt

    With Me
    .Subform1_ctl.Requery
    Set rst = .Subform1_ctl.Form.RecordsetClone
    lngCount = rst.RecordCount

    ' Display blank record if no records:
    If lngCount = 0 Then lngCount = 1
    If lngCount >= MaxRecs Then
    .Subform1_ctl.Height = SubfrmMaxHt + lngScrollbarHt
    Else
    .Subform1_ctl.Height = (lngCount * SubfrmDetailHt) + SubfrmHeaderHt + lngScrollbarHt
    End If

    ' determine ht for main form Detail section:
    .Detail.Height = .Subform1_ctl.Height
    DoCmd.RunCommand acCmdSizeToFitForm
    .Subform1_ctl.SetFocus
    End With

    Exit_Sub:
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "RESIZE FORM ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Sub

    Note in example Max Records to display is set to 10; this (and the other constant/variable values) can be adjusted as necessary. Most of the values can be determined in design view for main form & subform; the only real "variable" in the equation is, if the subform displays built-in navigation buttons, the height is based on the user's Windows Display/Appearance settings (the Scrollbar setting, which is measured in pixels). Several Windows API functions are used to determine the screen resolution, twips per pixel, etc., necessary to perform these calculations (see modScreenAPI module for API declarations). Note that all values must be converted to twips when setting height, width, etc in VBA (1440 twips = one inch). In tests, the form resized itself correctly at 800 x 600 and 1024 x 768 screen resolutions.

    It may or may not be practical to adapt this technique for your own project. NOTE: Corrected error in sample code.

    HTH
    Attached Files Attached Files

  15. #15
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table (97)

    Thanks Mark D, that v kind of you.

    I shall have a good look at this!
    Thanks,

    pmatz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •