Solved

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

Posted on 2012-04-12
10
317 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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