Solved

adding a non-d.b. value to a list view in an Access form

Posted on 2012-04-12
10
316 Views
Last Modified: 2012-06-27
I have a simple form, "Positions" for hiring, it's a simple list of open positions.

I want to have a field that displays "number of candidates being considered", but this isn't working out.

I was able to create a query that shows the count, but when putting this as the recordsource for the form, then I can't create new records.

So the data should look like this

Position              Department              Num of Candidates
Mktg Director          Mktg                                    3
Sales Director         Sales                                     2

What's the approach for this ?

I have a query with the number but not sure how to implement.
0
Comment
Question by:Alaska Cowboy
  • 6
  • 4
10 Comments
 
LVL 10

Assisted Solution

by:Michael Vasilevsky
Michael Vasilevsky earned 250 total points
ID: 37840426
You won't be able to have a form datasheet with a recordsource that uses a calculated field be able to add (or edit) records natively. You'll need to create a pop-up form, for example.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37840627
ok, how about when hovering the mouse over the Position Description, a hint tip displays ? If so, I need some help with that, not sure of the syntax.

I would try

dim numCandidates

sql = "select count(*) from position_candidates where position_id = " & Me.PositionId
[run query]
numCandidates = sqlrResult

Me.PositionDescr.HintTip = numCandidates & " candidates for this position
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37840631
wouldn't the other way of doing be an unbound form ? Just wondering, I wouldn't attempt to do that, just getting started with Access 2010, I was half decent on Access 2000 about 4 years ago but really rusty now, plus using Access 2010 for the first time.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 250 total points
ID: 37840639
Yep you could use an unbound form. That would be the way I would do it, rather than a control tip. Just load the controls on form open and add a save or update button. One nice trick I use is name the controls the same as your table field names then you can use the below code to populate them without having to specify each one individually:

    Dim rst As New ADODB.Recordset
    Dim tblName As String
    Dim x As Integer, y As Integer
    Dim frm As Form
    
    Set frm = Forms!MyForm
    tblName = "tbl_MyTable"

    rst.Open tblName, CurrentProject.Connection, adOpenStatic, adLockPessimistic
    rst.Find rst.Fields(0).Name & "= " & MyRecordID

    x = 1
    Do Until x = rst.Fields.Count
        y = 1
        Do Until y = frm.Controls.Count
            If frm(y).Name = rst.Fields(x).Name Then frm(y) = rst.Fields(x).Value
            y = y + 1
        Loop
        x = x + 1
    Loop
    
    rst.Close
    
    Set rst = Nothing
    

Open in new window

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37840735
ok, thank you. but I think I'll have to get some more of the basics down and kind of need to go with the hint tip for now, can you help with that.

do you do all your forms unbound ?
0
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37840751
Yep unbound forms reduce data corruption issues that could result from having a bound form sending data across a network.

Everything you need to know about ToolTips is here: http://support.microsoft.com/default.aspx?scid=kb;en-us;119991
Best regards,

MV
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37840779
MV, ok, thanks. still beyond my skill level and time constraints.

can you help me with this ?

dim numCandidates as Integer

sql = "select count(*) from position_candidates where position_id = " & Me.PositionId
[run query]
numCandidates = sqlrResult

Me.PositionDescr.HintTip = numCandidates & " candidates for this position
0
 
LVL 10

Assisted Solution

by:Michael Vasilevsky
Michael Vasilevsky earned 250 total points
ID: 37840824
You need a function to get numCandidates? Try:

Function GetnumCandidates(PositionId As Long) As Double
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT COUNT(*) AS NumCandidates FROM position_candidates WHERE position_id = " & PositionId

    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockPessimistic
    rst.MoveFirst
    GetnumCandidates = rst!NumCandidates 

    rst.Close
    Set rst = Nothing
End Function

Open in new window

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37840922
excellent ! Thank you.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 37841002
lot of work to do custom tooltips., will have to put on back burner.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question