Forms :: Return Data Instead Of The Record ID?
Nov 19, 2014
I have a form. That form has a combo box. That combo box is supported by a field in a table called Issues. That field is a Lookup field to another table called Contact List.
So my form combo box is populated via field in a table that references another table.
This works fine - and I can drop down and pick a person etc. Email address show up.
The form field is bound to column 1.
The issue:
I want to send an email to the person as selected by the combo box.
This too works, but the email TO: field contains the Record Number, not the text of the field.? How do I get the text of the combo box - which I can see, to appear in the TO field.
I am using a Dlookup:
=DLookUp("[MCCS Managers]","Issues","[ID]=" & Nz([ID],0))
this returns a ' 1 ' which is the record ID number from the Issues table.
MCCS Managers is the field name, Issues is the table, ID of course...
To be redundant, this cascaded field lookup works fine in the form. But my DLookup returns a ' 1 '. (the ID column, not the 'text' column.)
how do I get the text column to return?
View Replies
ADVERTISEMENT
Jul 30, 2013
I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.
I have the following code:
Private Sub cmdSaveTradeAndExit_Click()
DoCmd.RunCommand acCmdSaveRecord 'Save the current record
DoCmd.Close 'Close the current form
Dim CrId As Integer
CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord
Forms!frmTransactionMainActivePopUp.Requery
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
End Sub
But I am getting the following error:
Run-time error '2498':
An expression you entered is the wrong data type for one of the arguments
And the following is in yellow in the debug:
Code:
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
View 4 Replies
View Related
May 8, 2013
I'm trying to create a security measure that would prevent a user from accidentally paging down in a form and moving to the next record. I want to create an event macro that would fire if the user accidentally paged down into the next record. The macro would simply move back to the previous record.
What event would I use to build the macro ? What action would return to previous record ?
View 10 Replies
View Related
Jun 3, 2013
All using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code:
Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
View 13 Replies
View Related
Nov 12, 2014
I need to return a folders directory to a text box on my forms record called Files_Directory when i click the Browse command button... The folder will have more folders within it along with documents all relivant to the folder selected, hense the need for just the folder directory rather than a file.
View 12 Replies
View Related
Feb 6, 2014
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
View 3 Replies
View Related
Dec 24, 2014
The school director is going to input a date (by default today) and a subform will display all of the students who started before that date and haven't been closed as of that date. Next to each name, there will be 4 radio buttons (present, absent, holiday, hospitalized), which by default will be set to present. The director will go down the line, only needing to click when a student isn't present. At the end, she'll push a save button and the new records will be added.
My proposed method:
1. When the textbox with date input is changed, the subform (or just form and I put the textbox in the header?) will populate itself with a query based on student names whose corresponding startdate and enddate work with the inputted date.
2. The subform will be in continuous view so that it kinda looks like a data sheet but it has radio buttons rather than just spreadsheet cells.
3. When the save button is pushed, some visual basic code in the background will look at record 1, grab the student name and selected radio button value, and add a new record to the AttendanceRecords table with the name, attendance type, and date. Then the code will move to the 2nd record, rinse and repeat until it goes through all of them.
Questions/problems:
1. At its core, is this the Access way of doing something like this? Is there a simpler, more efficient, or generally smarter way of doing attendance?
2. How is step 3 going to work? I can look up how to add new records to a table, but I'm not sure how to move around from record to record collecting corresponding data.
3. Since the AttendanceRecord table uses studentID and typeID rather than actual names and actual attendance types, what's the easiest way of adding the new records with that data efficiently? I'm thinking of making the RecordSource of the subform some sort of linked up set of tables such that if I'm looking at record 1 and the name in the textbox on the subform is "Bob" I can just reference studentID in the background without needing anything on the form at all with studentID?
View 2 Replies
View Related
Feb 14, 2014
I have been searching all day for a solution. This is my first time using Access so a lot of this goes over my head. I'm currently running Access2010. I have a form that I am using for when people take office supplies and we can generate a cost per department. All of the Tables, Query and Reports are set up already and surprising work. My form fields are:
Date Created (automatic current date)
Quantity
Item Code (Combo Box that shows Item# and Item Description)
Initials
Department (Combo Box that shows Dept# and Dept Description)
RefCode
Remarks
I am trying to get it to where when I enter a new record it will autofill with all fields expect "Item Code" from the previous record until I change it.
View 3 Replies
View Related
Nov 11, 2013
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
View 14 Replies
View Related
Sep 20, 2005
My quiery is supposed to search through a table of names, and find the latest timesheet entry date for each name, as follows:
ID: 1
Name: John
Timesheetdate: 1/1/2005
ID is specific to each name.
I want the result to be a single row for each ID/name, and to return ONLY the latest timesheetdate (this could either be the maximum value or the latest entry).
I have a feeling that this is a simple problem, but i just dont have the knowledge to do it, any help would be greatly appreciated!
View 4 Replies
View Related
Feb 23, 2006
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?
View 10 Replies
View Related
May 26, 2005
I have a form in spreadsheet view. Basically all I want is to update the information which is viewed, but Form.ReCalc doesn't requery the database. So I tried Form.Requery. But after doing Form.Requery, the first record of the form becomes the active record, and in some cases I have to scroll to see the record that was active before the requery. Is there any way to avoid this?
One possible I solution might be to requery that form and return to the record which was active before the requery. Here comes another problem. The form has a subform in spreadsheet view (which is viewed by clicking + to the left of any record in the first form). If I expand an record to view the sub form, it is also expanded after the requery, but I also want the main form to scroll down so the subform is fully visible (or as visible as it was before the requery), or alternatively, scroll down so the top most record in the main form that is visible also is the top most visible record after the requery. If this is not possible, maybe there is a way to make the record which was the active record before the requery to be the top most visible record after the requery.
Could someone point me in the right direction? Give any suggestion?
Any help is appreciated.
View 8 Replies
View Related
Aug 15, 2007
Having problems with the following function to goto a specific record and return a value from a field in that record.
Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function
strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.
My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.
Thanks
Jeff
View 6 Replies
View Related
Sep 23, 2014
My setup is simple, it consists of two tables:
Employee (one)
Position (many)
So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.
I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?
View 10 Replies
View Related
Jan 31, 2005
This is probably the first of many questions. I have an Access2000 DB with two tables. Each has a field called ParID. I want to have a form with Table1 and Table2 listed. I want to query on a parcel number I keyin. If Table1 has the number in it, I want the form to note such. The same goes for Table2.
Example: Keyin: 123
If Table1 has 123 in it and Table2 does not, my form would note:
Table1= YES
Table2= NO
If they both had 123 in it:
Table1= YES
Table2= YES
etc.
Thanks,
SKK
View 3 Replies
View Related
Jun 29, 2005
Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other?
Table Name=SURVEY_RAW_DATA
Column Name=TOTAL_EXP
Column Name=TOTAL_NX
There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.
In SQL I would write it like this:
Select * FROM SURVEY_RAW_DATA
WHERE TOTAL_EXP > TOTAL NX;
Thanks in advance,
Ken
View 2 Replies
View Related
Feb 19, 2014
I'm new to Access and VB but I managed to write parts of what I want access to do.By tweaking a code I found on the internet, I managed to write a small Subroutine that allows the user to click on one of the fields of a record in a report and then Access opens the form on that specific record.*My code goes something like this:*
Code:
Private Sub Edit_Click()
* Dim strWhere As String
* Dim DocName As String
* DocName = "FormName"
* strWhere = "[Field Name]='" & Field & "'"
* DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub
Now I want to get the name of the Table where the record exists.So, let's say when I click on the "Field" it gets the name of the table where the record with that field exists and sets it in a variable.OR even better would be, get the name of the Form where that record exists but I guess that's a little more complicated since the record is directly linked to the table...*
View 14 Replies
View Related
Feb 23, 2014
I'm trying to create in access2010
(1) a query that returns the latest record (newest) in a table called 'Invoices' and then
(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.
Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.
Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?
View 4 Replies
View Related
May 21, 2013
I have a query that returns records in datasheet view. I need a field to accept the user selection from a drop down, then save that to a table. The query is based on a couple queries and one of those queries is based on the table i need to save the user input into. When i make a selection from the drop down list it puts the selection in that field for all the records and then doesn't save.
View 1 Replies
View Related
Nov 7, 2006
I have a table like this
Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...
I want to do a query to return just one row per unique ItemNo
So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog
I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.
I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.
View 3 Replies
View Related
Aug 18, 2014
I have two tables with a one to many relationship. The tables are linked by the INDEX column.
EXAMPLE:
Code:
TABLE_1
INDEX NAME
1 Name_A
2 Name_B
3 Name_C
TABLE 2
INDEX NUM_INDEX STATUS
1 1 REJECTED
1 2 REJECTED
1 3 OPEN
2 1 CLOSED
3 1 REJECTED
3 2 OPEN
I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).
Example:
Name_A, OPEN
Name_B, CLOSED
Name_C, OPEN
SQL that I have now.
Code:
SELECT A.FIN_Finding_Number, B.Max_Index
FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index
FROM TBL_Response GROUP BY RES_Finding_Index ) AS B ON A.FIN_Finding_Index = B.RES_Finding_Index
WHERE (((A.FIN_Finding_Index)=34));
This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.
View 2 Replies
View Related
Mar 12, 2014
I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:
Timestamp EquipmentNumber Status
Today ------Machine1 ----------Running
Today ------Machine2 ----------Running
Yesterday -Machine1 ----------Down
Yesterday -Machine2 ----------Running
There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:
Code:
SELECT TOP 1 TestCompressorRoundQuery.LoggedAt, TestCompressorRoundQuery.AssetNumber,
TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes
FROM TestCompressorRoundQuery
WHERE (((TestCompressorRoundQuery.AssetNumber)="104399"))
UNION ALL
[Code] ....
I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.
View 6 Replies
View Related
May 30, 2014
I have a database where I need to return only the first record of each key (named "process key") accordingly to a time frame I set.
Consider the table below:
Code:
ProcessContact dateSalesmanClientClient
KeyKey
99904/28/2014 11:39:00SALESMAN 1111CLIENT 1
88804/28/2014 10:13:00SALESMAN 1222CLIENT 2
55504/22/2014 14:29:00SALESMAN 2333CLIENT 3
66604/16/2014 12:14:00SALESMAN 3444CLIENT 4
55504/16/2014 11:47:00SALESMAN 2333CLIENT 3
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
View 9 Replies
View Related
Jan 21, 2014
My boss has a form based on a rather large table with a lot of records/fields and she wants to be able to have a field where she can enter something and it will seach every record in the table and return the results in a table. How do I do this?
View 9 Replies
View Related
Oct 23, 2013
I'm using Dmax() to return a max number in a field which I then want to inc by 1 for a new record. Dmax is returning 999. I believe Dmax therefore thinks it's a text field. So where do I change this to a number field?
View 2 Replies
View Related
Jul 16, 2014
For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....
etc.
I could then quickly count how many obs periods started within the desired month.
View 14 Replies
View Related
