Solved

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

Posted on 2012-04-12
10
310 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now