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?
I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now i want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record source dropdown.
View 5 Replies View RelatedI am building a database where one Form displays records from 14 different tables. For some reason, when I recently try to add a field on to a form from a new table, the ENTIRE form loses the record source, and every single field that is already on the form gets that green dot in the corner with errors surrounding a record source that cannot be found. What am I doing wrong? Am I exceeding some limitations with forms?
View 4 Replies View RelatedI 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.
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.
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.
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
Hello,
I think i've done this before but I can't seem to remember the process so any help would be great. What im trying to accomplish here is I have a database that will be updated(new data appened) to on a daily basis from many other databases, but the data that is to be appened can come from various destination drives ie (C: drive, D:drive, CD, Thumbdrive, etc). I set up a dialog box that prompts the user to select the source database, but im not sure how I can store that source destination string and update my append query with that source database string. I believe there is a way to do this. Am I making any sense? Any help would be so great. Thank you in advance.
-James
We having this Human resource system that the company will be using however, the current payroll application will still be used as normal but the update will not be done on the same application.At the moment the current set up is that there a three database linked together that makes up the payroll application but for now one of this database will not be pupolated with data as it will now come from another source all together via extraction or file interface which is the new HR software.
The only problem I'm facing is some of those fields that i will need to transfer over to suit the current set up of the payroll application is not included on that new software file extract and i dont know where to start or what to do because abviously the file interface should consists of this mandatory field.Without seeing the relationship of what I'm talking about do you think its worth continuing this project as those wanted field are not included?And how can we go without these mandatory field?
I have a main form that has 10 sub forms Each sub form’s record source is link to a different Query.
It takes more then a minute to open the form, (because it’s 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, it’s adding the following:
Me.SubMySub.Form.RecordSource = "select * from qMyQuery"
Me. SubMySub.Form.Requery
However, after I close the main form, the sub form’s 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?
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 don’t 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
Hi, I need help on cascading combo boxes, in my form
In my example DB i have 3 tables(when i find a soloution i will implement it on a larger scale database which will have around 15 tables). I need to create reports of these tables. The problem was that i need to filter the data before i create the report by 2 fields. If i was to do this the straight forward way i would have 3 tbls, 3 qrys and 3 reports (which on my final database would mean 15 of everything.)
So i've decided to use a form driven system to reduce the number of forms and querys i have in the DB. Using a form based system i should have 3 Tables (called 1, 2 and 3), 1 Query (called query2), 1 Form (called withselect) and 1 report (called rptquery1).
Now to the system.
The form should have 3 combo boxes. 1 at the top to select the table i want to query. Then 2 others to select the fields by which to filter. the form should also have 2 buttons, 1 torun the report one to return reults in a table to edit the data if needed.
This is the current problem i am having
In the forms 1st combo box i have managed to list all my 3 tables (thanks to FancyPrairie) using the following code:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (Left$([Name],4)<>"MSys") AND (Left$([Name],4)<>"USys") and (Left$([Name],1)<>"~") and (MSysObjects.Type=1)
ORDER BY MSysObjects.Name;
But i cant select any thing in the following 2 comboboxes. At the moment im tryin to use this code in the record source of the 2nd and 3rd combo boxes
SELECT * FROM YourTable WHERE (YourTable.TableName=[Forms]![withselect]![Combo6]);
Attached is the latest version of the DB.
Im getting the error:
The record source 'SELECT * FROM YourTable WHERE (YourTable.TableName=[Forms]![withselect]![Combo6]);' specified on this form or report does not exist.
Hopefully someone can help.
Hello, I have a form with a subform. I want to change the record source on the subform during an OnClick event. I am not sure what I'm doing wrong, but I get a "object does not support this method" error. Can anyone help? Thanks in advance.
View 1 Replies View RelatedHeya. I hate posting yet another cascading query but I just poured through the first 40 bits and couldn't find something similar.
Basically, I seem to have an errored idea in how to effectively used cascading boxes on a larger form-wide scale.
Basically I have a form with an unbound combobox in the form header: cboSchool
The detail section should only have two fields, StudentID (txtStudentID) and a boolean (bolOT) both of which exist in a specific table source (tblStudents). StudentID is a locked field.
What I would like to do is only have the StudentID's available that are valid for the school. I attempted using a query in the record source that would reference cboSchool but I keep ending up with blank forms. (There's a string of queries that link the schools found in cboSchools to StudentID)
Any ideas?
~Chad
Hello,
I'm having a strange problem and was wondering if anyone can help me out. I have a form that is for inputting and modifying data in one table. It used to work when you opened the form you would see x amount of records. Now it's opening to 1 blank record. If you hit filter it fliters like 1700 records and then when you unclick it, you get the entire 10000 records. I thought it had something to do with the Record Source in the properties but when I fixed that it still didn't work. Any ideas?
Thank you!
Hi,
I am trying to create a textbox that will show a document number of the last record so when a user clicks add new document he/she knows what document number to enter in next. It must not involve the user looking a subform with the document numbers on it.
Thanks for your time,
Leon
I have 2 forms. On form1, the record source is a query. From form2, I need to iterate through the recordset from form1 and perform some action. How do I access the recordset from form1?
Thanks in Advance,
-jnoody
OK. I have a report that I want to use as a master and use with about 4 different querys. In the report properties I've bound it to a query. I've tried for ages to change the record sources with on click command bottons on another form. I think things have become complicated because there's a subreport on the report I AND a there's bunch of code to make things invisible in the on page event of the report.
anyway, here's what I'm using:
DoCmd.OpenReport "rpt_master", acViewPreview
Me.RecordSource = "qry_rptPrintRollClass"
Is this the right way to do it??
Any ideas on how I can clean it up??
Thanks
Damon
Hello once again,
I am wondering how I can display information on a form that is not of the same record source without using a subform.
Right now, I have a form setup with qryAll as the record source which provides the information to a listbox i have setup on the form. When I select something in this list box, I want to display information related to it from a table based on a CenterID. Is this possible to do? I won't need to edit the values, I just need to display them into a textbox or something.
Jared
This code loads a new subform in the frame. It then scopies a value to an input field but then i need to set the record source and display the data. This latest pasrt does not work.I've been trying for some time now by changing the part before recordsource but no luck.
Code:
gekozenwo = Me.Keuzelijst0
Forms!switchboard.subfrmWindow.SourceObject = "frmInformation"
Dim frm As Form
Set frm = Forms!switchboard.subfrmWindow.Form
frm.DataEntry = True
Forms!switchboard.subfrmWindow.Form!Tekst33 = gekozenwo
MsgBox gekozenwo
frm.RecordSource = "SELECT * , [orders_maint_detail]![woisnr] & ' / ' & [orders_maint_detail]![Ordernummer] AS Expr1 " _
& "FROM orders_maint_detail " _
& "WHERE (((orders_maint_detail.woisnr)='" & gekozenwo & "'));"
Me.Requery
wonr = gekozenwo
I know my form is large and got too many fields for a standard form but trying to expand it anyway and don't really want to go to sub-forms...as then cannot expand it without re-creating forms/queries/tables structure. (or that what i believe)
My form and subsequent table is nearing the 255 limit, so I created a second table and just used a query to put both in form, but then I found out that a query also has the limit...
So if have two queries set up from each table but to same form?
I have been working on quite a few multi user Access projects in the past few months with a backend and a front end which has linked tables.
I would like to know if there is any way to find out the source of a table linked to the front end and where would its location be on the network.
I have Form A that has a combo box and a "Submit" button. When the user clicks on the Submit button it needs to open Form B. Recordsource of this Form B is "Query B"
Item selected on the combo box becomes the criteria for "Query B and Form B needs to be opened based on that criteria.
What is the easiest and fastest way (no dlookups please!) to do this?
If an illustration is possible through an example that will be wonderful.
Thanks in Advance.
I am making a master form for three tables which ultimately form a hierarchy. I am getting all the fields from each table onto one form. By default the record source would be made up of an INNER JOIN such as below
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession FROM (PERSON INNER JOIN CONTRIBUTOR ON PERSON.id=CONTRIBUTOR.id) INNER JOIN MUJAHED ON CONTRIBUTOR.id=MUJAHED.id;
I'm trying to change the record source to match using the where clause instead. However the following allows me to view but not add a new record
SELECT PERSON.first_name, PERSON.country, PERSON.last_name, PERSON.street, PERSON.area, PERSON.city, PERSON.telephone, CONTRIBUTOR.registration_date, CONTRIBUTOR.fav_category, MUJAHED.profession
FROM PERSON, CONTRIBUTOR, MUJAHED
WHERE (PERSON.id = CONTRIBUTOR.id) AND (CONTRIBUTOR.id = MUJAHED.id);
A tip or guideline on how I can correct this will be appreciated.
Regards
I have a form where users enter data in various ways - combo boxes with drop down selections and text boxes where users manually enter some data. I also have several text boxes that return "answers" based on calculations from user entries mentioned above. The form is bound to a table where all the entered data is stored. I'm having trouble saving the calculated information to the bound table. It's easy in all the non-calced boxes, I just put the column name of the bound table in the control source, but in the calculated fields, I already have the formula in the control source. How can I identify the proper column to save the data in this case.
Thanks!
I'm looking to change the record source of a report and then print the report depending on what button the user clicks on.
Is there some way to do this?