Modules & VBA :: Return Name Of A Record Source Table

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 Replies


ADVERTISEMENT

Modules & VBA :: No Current Record - Unknown Source

Sep 11, 2013

I am receiving the No Current Record message, but have been unable to determine the code that generates it. The database is a system that creates tasks and assigns them to users. In the header section of the Tasks form are several controls they can use to select Active or Completed tasks, refresh the data, sort by columns, etc. There are also three other forms (not subforms) that lay on top of the Tasks form to provide additional detail about the current task.

If they "complete" all their active tasks and click Refresh, all the tasks disappear, as they should, and everything works fine. If they then try to close the form, or click any control in the Tasks header, the No Current Record message pops up. I've put debug stops in every conceivable location, but the message appears to occur before any code is activated.

I did find that I can make the message not occur by commenting out the code that synchronizes one of the associated forms (Notes), but I still don't know what is triggering the message. Debug stops in Notes yield no results either, and clicking a control in the header of Tasks shouldn't affect Notes.

How to find out what's triggering the message?

View 5 Replies View Related

Modules & VBA :: Open Filtered Form With Record Source Set

Dec 17, 2013

In my Access adp, when I open a certain form (frmVertebrates), the databinding occurs in the Load event for the form, for various reasons. I have not specified any datasource in the form's design view.

Databinding for frmVertebrates:

Code:
Me.Form.RecordSource = "select * from dbo.vertebrates where catalogID=3"

This works great when just opening the form. However, when opening with a filter from a button on another form it won't work, the form displays all records.

Button code:

DoCmd.OpenForm frmVertebrates, , , "vertID=123"

Obviously, this makes sense, since the record source is explicitly set in the Load event.

Is there a way to capture the filter "vertID=123" so it can be added to the Form.RecordSource sql?

When breaking in the Load-code and watching the 'form' variable, I can't spot the filter condition anywhere...

View 4 Replies View Related

Modules & VBA :: Runtime Error 2580 - Record Source Does Not Exist

Jun 9, 2015

I currently have this set as the forms default recordsource (which works just fine):

Code:
SELECT TOP 5 tblUsers_Phone_Book.EMAIL_ADDRESS, weightedDL('me@mine.com',[EMAIL_ADDRESS]) AS Expr1

And I have this vba to dynamically switch around that email address.

Code:
Private Sub Form_Load()
Dim intPos As Integer
Dim strControlName As String
Dim strValue As String
Dim sSQL As String
If Len(Me.OpenArgs) > 0 Then
' Position of the pipe

[Code] ....

If I msgbox the sSQL - it shows identical to the default recordsource but I get the error:

The error message I get is:

Run-Time error '2580'
The record source 'SELECT TOP 5 tblUsers_Phone_Book.EMAIL_ADDRESS, weightedDL('me@mine.com',[EMAIL_ADDRESS]) AS Expr1' specified on this form or report does not exist.

I tried copying the exact working default sql into the vba and get the same result.

View 3 Replies View Related

Modules & VBA :: Changing Record Source On A Form - 2 Different Outcomes Using Same Bit Of Code?

Apr 28, 2015

I'm changing a record source on a form when the user clicks a "save" button.

I'm doing this to store a value from the current form in a table that is not part of the default form query.

This works fine.

I have a separate button on my form called "Home", when the user clicks this button it requerys the current form which triggers the before update event to run, this in turn brings up a message box which asks the user if they want to save or not. If they click yes then it runs the SaveButton click() code.

For some reason when the user presses the save button and then exits, everything works but if a user presses the “home” button which triggers the save button then it brings up the “2107 The Value you entered doesn’t meet the validation rule defined for the field or control.” Runtime error and stops on the change recordsource command.

Why triggering the same code directly from a button or indirectly from a before update event has two different outcomes.

View 4 Replies View Related

Modules & VBA :: Update Access Table From SQL Server Table Source

Jun 17, 2013

I have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list.

Code:

Option Compare Database
Sub LocalServerConn_Test()
Set conn = New adodb.Connection
Set rst = New adodb.Recordset

[code]....

View 4 Replies View Related

Form - Record Source - Select A Table

Nov 13, 2013

Form - record source - select a table that when the user enter a value in the field of the form it gets place into the designated table (select a table). my problem is that I have two fields in the form, one field from the data entry is suppose to go to the train table,and another field the data entry is suppose to go to the station table. if I have the form's record source have a designated table for the data entry.It doesn't have two tables for the data entry.

View 13 Replies View Related

Queries :: Return Last Record In A Table

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

Queries :: Return Latest Record In Table

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

Return Only One Record From Table With Repeating Values In Fields

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

Tables :: Search Through Every Record And Return Result In Table

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

Remove Record Source/Control Source

Sep 19, 2005

I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.

I'm trying to do a

Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""


but this does not seem to work in actualy removeing the record source and control source.

View 14 Replies View Related

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this

[strField]
"kick the ball"
"return the pass"

my query result should looks like this

[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

View 3 Replies View Related

Modules & VBA :: Return Folder Directory To Text Box On Forms Record When Click Browse Command Button

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

Modules & VBA :: Return Single Value From Table And Assign It To String - DLookup Not Working

Sep 3, 2014

I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"

Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub

View 13 Replies View Related

General :: Find Record From Field In Subform And Then Return Its Parent Record

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

Modules & VBA :: Split Table Record Into Multiple Records / Rows In A New Table

Nov 10, 2014

In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.

the data has a fixed part lets say

Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this

Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...

What is the best approach?

View 3 Replies View Related

Modules & VBA :: Copy Record To History Table And Then Delete It From Main Table

Jul 9, 2014

I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.

what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.

I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.

'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next

[Code] ....

View 8 Replies View Related

Modules & VBA :: Generate Multiple Records In One Table From Single Record In Another Table

Sep 20, 2014

I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.

I have one table with the following field and data:

ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5

I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:

ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green

The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.

View 8 Replies View Related

Modules & VBA :: Add Rows To Table Based On Record Count Of Another Table

Mar 10, 2015

I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.

View 8 Replies View Related

Set Record Source Of Combo Box

Apr 25, 2005

Hi all,

I have a form with a combo box on it and what I would like to do is when I click a command button, change the record source of the combo box from the query I currently have to a different one?

Is this possible,

Thanks for your time.

Taff.

View 8 Replies View Related

Record Source From Another SQL Database.

Nov 2, 2006

I have multiple SQL databases and on most have searches built on stored procedures.

I'd like to create a central search database but can't get Forms to go across SQL databases to get their record source.

Am I being thick?

View 2 Replies View Related

Record Source Does Not Exist

Oct 7, 2004

I have set up a simplified event booking procedure, similar to the one generated by the access event manager wizard. It uses three tables, one with attendees details, one with the event details and abooking table that links the attendee ID with the Event ID.

The booking table uses commands similar to SELECT[Names + Contacts].[ID],[Names + Contacts].[Surname]FROM[Names + Contacts] to generate drop down lists of surnames and events so that the two can be matched together.

This works fine as a table.

However when placed into a form, whenever the form is opened I get an error message saying the record source specified on the form does not exist.

I have tried regenerating the form, etc. I have used a wizard to generate the form, closed the form, not changed anything in the database and then tried reopening it and I get the error message, can anyone shed any light?

Also if you accept the message then it knows how many records there are, so it must know there's something behind it, also if you change to design mode and then back to form mode, it finds all the data.

View 4 Replies View Related

Help With Record Source And Recordset

Jan 24, 2005

I have a form with a record source already defined (used so the user can browse through records and edit them)
I have the form store requirement ids and not the name however I would like to present the requirement name rather then the id to the user
So it tried with a sql query and a recordset but I keep getting the error data type mismatch in criteria expression on this line
Set rsm = dbm.OpenRecordset(sRequirementName1)

I think the problem is having the record source already defined and then having a new Recordset not sure how to work around it

here is the code
----------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Next_Click()

Dim sRequirementName1 As String
Dim queryResult As String
Dim wsm As workspace
Dim dbm As Database
Dim rsm As Recordset

sRequirementName1 = "SELECT Requirements.RequirementName " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementID = '" & Me.RequirementID11.value & "' "


Set wsm = DBEngine.Workspaces(0)
Set dbm = wsm.OpenDatabase("E:Practicum Hours.mdb")
Set rsm = dbm.OpenRecordset(sRequirementName1)

queryResult = rsm.Fields(0)

Me.HoursCompleted11_Label.Caption = queryResult


End Sub

----------------------------------------------------------------------------------------------------------------------------------------------------------

I have also attached the DB if anyone thinks they can help, if you are confused about what i am trying to do with the modify form i made one with students that works
it is the modify hours1 form and the code above is the on click event for the next button

Thanks for any help anyone might offer

View 2 Replies View Related

Change The Sub Forms Record Source

Nov 16, 2005

I have a main form that has 10 sub forms Each sub forms record source is link to a different Query.

It takes more then a minute to open the form, (because its running the query for all sub forms). So I changed the sub forms source to SELECT * FROM tblTest WHERE false;

I also changed the main form. When the button on the main form is clicked, its adding the following:

Me.SubMySub.Form.RecordSource = "select * from qMyQuery"
Me. SubMySub.Form.Requery

However, after I close the main form, the sub forms record source stays linked to
SELECT * FROM qMyQuery;

And will take the same long time again to open the main form.

Does anyone have any solution?

View 7 Replies View Related

How To Use Same Form By Changing Record Source?

Feb 8, 2005

Hi all,

I have Form F_CashSalesHead with a subform F_CashSalesInvFoot with one-2-many relationship on their tables. Subform contains a checkbox field that I use to lock the record set (On a command button click it runs one update query to add value 1 to each checkbox to make Enable=False all the records of current invoice on the form).

One-2-many relation ship is made on InvNum field in both tables.

When I open F_CashSalesHead form, bcz of some code line I wrote on On Load event of F_CashSalesHead , at the beginning it give massage how many invoices are pending to lock and would you like to see. If click Yes to see list, it opens a small form that called F_Count_Unlocked_Invoices showing invoice numbers and unmarked checkbox which is pending to lock. This small form is based on following query,

SELECT DISTINCTROW T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName, Sum(T_CashSalesInvFoot.Lock_Cash_Inv) AS [Sum Of Lock_Cash_Inv]
FROM T_CashSalesInvFoot
GROUP BY T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName
HAVING (((Sum(T_CashSalesInvFoot.Lock_Cash_Inv))=0));

This works fine.

What I am looking for is, I want to use the same F_Count_Unlocked_Invoices form for Credit Sales invoice also with the same trick. Because I dont want to create another same form and write code that help to increase size of db.

Can it be done just by changing record source of form F_Count_Unlocked_Invoices? Or what is the way to do it?

With kind regards,
Ashfaque

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved