Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-20-2019, 08:17 PM   #16
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Form OK in Excel, but not in Access.

Hey, that's beaut, thanks. Good to see someone elses concept.. it's given me some ideas to try with building and passing the recordset. I also use a function but don't call it from the form. Instead I use a Global which the Form uses, so will change that. You're only showing the Height and width values. My Form is Popup & model (over another Form).. but I had no end of trouble setting the Height/Width/Position until using Docmd.MoveSize. But it's still not 100% as it won't resize the columns and keep the changes.. and it acquires a horizontal scroll bar (even though it supposedly Off) leaving a huge empty space to the right as the scrollbar is moved.
No big deal but would be worth knowing why they're there and how come some actions and Property settings are ignored.

Again, thanks for your help and time.. Cheers

kirkm is offline   Reply With Quote
Old 10-21-2019, 04:35 PM   #17
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Form OK in Excel, but not in Access.

Hi DBGuy,
At the risk of going on and on about this.. I was able to implement the sample you gave in FormDemo when you call a Function to set the Recordset after the Form loads.
However I need to know the RecordCount before opening the Form. Only if it's 2 or more is the Form needed. This meant I was setting the recordset once to get a count, then again as the Form loads. That didn't seem right. Incidentally why is Me.OpenArgs always (or mostly) Null the first time you run the code?

I changed to your example in Database3, using Set Frm, .visible method and passing the recordset as property.
This worked fine, after I realised that way neither Form_Load or Form_Activate were called. Form_Current seems to work though.

So I think it's okay like this and was great getting your input.
kirkm is offline   Reply With Quote
Old 10-21-2019, 04:55 PM   #18
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Form OK in Excel, but not in Access.

Quote:
Originally Posted by kirkm View Post
Hi DBGuy,
At the risk of going on and on about this.. I was able to implement the sample you gave in FormDemo when you call a Function to set the Recordset after the Form loads.
However I need to know the RecordCount before opening the Form. Only if it's 2 or more is the Form needed. This meant I was setting the recordset once to get a count, then again as the Form loads. That didn't seem right.
You could try assigning the recordset to a variable, so you can manipulate it first before assigning it to the form.



Quote:

Incidentally why is Me.OpenArgs always (or mostly) Null the first time you run the code?
Hmm, it's never null when I ran it. How are you checking this?


Quote:

I changed to your example in Database3, using Set Frm, .visible method and passing the recordset as property.
This worked fine, after I realised that way neither Form_Load or Form_Activate were called. Form_Current seems to work though.

So I think it's okay like this and was great getting your input.
Not sure I follow this one. Where did you get Database3?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-21-2019, 05:14 PM   #19
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Form OK in Excel, but not in Access.

> You could try assigning the recordset to a variable, so you can manipulate it first before assigning it to the form.
That's what I did.
>Hmm, it's never null when I ran it. How are you checking this?
After typing in the code then running it. You'd checked for null first, I thought this was why.
>Not sure I follow this one. Where did you get Database3?

From you. See Msg #9
kirkm is offline   Reply With Quote
Old 10-21-2019, 05:58 PM   #20
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Form OK in Excel, but not in Access.

Quote:
Originally Posted by kirkm View Post
> You could try assigning the recordset to a variable, so you can manipulate it first before assigning it to the form.
That's what I did.
So, does that mean it's working now? If not, can you show us how you used a variable? I can't imagine why using a variable won't work.
Quote:

>Hmm, it's never null when I ran it. How are you checking this?
After typing in the code then running it. You'd checked for null first, I thought this was why.
No. I check for Null in case the user opens the form by double-clicking on it from the Navigation Pane. This is actually a common practice.
Quote:

>Not sure I follow this one. Where did you get Database3?

From you. See Msg #9
I see. But, didn't you say you modified it? If so, can you post the modified version, so I can see what you mean? By the way, if you're using the Current event, just be aware it will fire again each time the user navigates to a different record on the form.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-21-2019, 06:12 PM   #21
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Form OK in Excel, but not in Access.

What would be a better choice than Current event?
Here's what I have... hope it makes some sense being a subset..

Code:
  Dim sql As String, sArtist As String, sTitle As String, pArtist As String
    Dim rDisk As DAO.Recordset
    Dim frm As Form
    
    sTitle = Nz(Me.subCDTracks!cboTTitle)
    sArtist = Nz(Me.subCDTracks!cboTPerformer)
    If Len(sArtist) > 5 Then pArtist = Mid(sArtist, 5) 'Remove any leading "The "

    
        'Absolute Match Artist AND Title
    Set rDisk = GetAbsoluteMatch(pArtist, sArtist, sTitle)
    If rDisk.RecordCount <> 0 Then rDisk.MoveLast
    If rDisk.RecordCount = 1 Then
            ApplyIt rDisk
            Set rDisk = Nothing
            Exit Sub
        ElseIf rDisk.RecordCount > 1 Then
            Set frm = Form_frmExtSingles
            With frm
                .RS = rDisk
                .TheCaption = MyCompany() & " Artist & Title = " & Replace(sArtist, "The ", "(The) ") & " - " & sTitle
                .Visible = True
            End With
            Set frm = Nothing
            Set rDisk = Nothing
            Exit Sub
    End If



Function GetAbsoluteMatch(pArtist, sArtist, sTitle) As DAO.Recordset
    Dim sql As String
    sql = "SELECT " & MyCompany & ".Artist, " & MyCompany & ".Title, " & MyCompany & ".Label, " & MyCompany & ".Year "
    sql = sql & "FROM [" & MyCompany & "] "
    sql = sql & "WHERE " & MyCompany & ".Artist = p0 "
    sql = sql & "AND " & MyCompany & ".Title = p2 "
    sql = sql & "OR " & MyCompany & ".Artist = p1 "
    sql = sql & "AND " & MyCompany & ".Title = p2 ;"

    With CurrentDb.CreateQueryDef("", sql)
        .Parameters("p0") = sArtist
        .Parameters("p1") = pArtist
        .Parameters("p2") = sTitle
        Set GetAbsoluteMatch = .OpenRecordset
        .Close
    End With
End Function





Option Compare Database
Option Explicit

Private Sub Form_Current()
    DoCmd.MoveSize 7500, 5000, 16000, 8000
End Sub

Public Property Let RS(x As DAO.Recordset)
    Set Me.Recordset = x
End Property

Public Property Let TheCaption(z As String)
    Me.Caption = z
End Property
kirkm is offline   Reply With Quote
Old 10-21-2019, 06:34 PM   #22
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Form OK in Excel, but not in Access.

Quote:
Originally Posted by kirkm View Post
What would be a better choice than Current event?
It depends. If you want the code to fire each time the user navigates to a different record, then the Current event is perfect. Otherwise, what happened with the Load event?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-21-2019, 06:57 PM   #23
kirkm
Newly Registered User
 
Join Date: Oct 2008
Posts: 700
Thanks: 23
Thanked 5 Times in 5 Posts
kirkm is on a distinguished road
Re: Form OK in Excel, but not in Access.

Neither Load or Activate events fire the way the Form is opened (unless I've missed something).
Maybe I could move it to a Sub and call it before .Visible? Will try... no need to run it every time a new record is clicked.
kirkm is offline   Reply With Quote
Old 10-22-2019, 07:08 AM   #24
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,914
Thanks: 57
Thanked 1,293 Times in 1,274 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Form OK in Excel, but not in Access.

Quote:
Originally Posted by kirkm View Post
Neither Load or Activate events fire the way the Form is opened (unless I've missed something).
Maybe I could move it to a Sub and call it before .Visible? Will try... no need to run it every time a new record is clicked.
Okay. Let us know how it goes...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Excel form to an Access form Milade8080 Forms 1 11-11-2016 09:49 AM
Excel form in Access Khushalay Forms 10 08-15-2016 03:23 AM
How to display the access form in the excel form dhaya123 Reports 8 12-19-2013 12:40 AM
Open Excel file from Access form, populating certain fields in the Excel file... michaeljohannes Forms 15 01-12-2011 12:42 PM
Excel vs Access for order form TIbbs General 4 03-06-2009 08:28 AM




All times are GMT -8. The time now is 01:46 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World