?
Solved

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

Posted on 2012-04-12
10
Medium Priority
?
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 10

Assisted Solution

by:Michael Vasilevsky
Michael Vasilevsky earned 1000 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 1000 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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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