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 a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done
2)If it can be done, how can I do it?
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.
I have a form F_EXTRACT whose record source is a table named T_EXTRACT.
The form contains 2 controls, a Text Box named Surname and a Combo Box named Member_ID.
Column 1 of the combo box is bound to Member_ID (in the T_EXTRACT table).
Column Count is set to 3.
In Row Source, I have the following SQL statement:-
SELECT [T_MEMBER].[Member_ID], [T_MEMBER].[Surname], [T_MEMBER].[Initials]
FROM T_MEMBER
WHERE ((([T_MEMBER].[Surname])=[Forms]![F_EXTRACT]![Surname]))
ORDER BY [T_MEMBER].[Surname], [T_MEMBER].[Initials];
I wish to populate T_EXTRACT with selected Member_IDs but as the MEMBER_ID table contains many hundreds of records need to reduce the search through the display in the combo box.
The above method (by entering a surname in the Surname text box) works fine for the first entered surname but second and subsequent surname entries result in the details for the first entered surname being displayed in the combo box.
I've tried binding Surname to a field in the F_EXTRACT table but this makes no difference.
Any ideas would be much appreciated.
Hiya! So, I have a form with a cascading combo-ish type solution. In actuality it's an option group that helps feed a combo-box. The option group, however, is unbound and the two choices represented in it span several choices elsewhere.
The field I'm reducing with a cascading solution is my [ExpenseCode]field. Each [ExpenseCode] is assigned one of 6 [ExpenseType]'s, numbered 0-6. Deductible expenses range from 1-4, non-deductible expenses cover 5, and 0 is used only by the system in the creation of dummy records and is unnecessary to the data-entry form. Using the [ExpenseType] directly would be confusing to the end user but is important to us during aggregation.
I am attempting to use an option group to simply select "Deductible" or "Non-Deductible" from the listed fields and have my combo-box update itself in kind.
The form is: frmExpenseLogsEntry
The option group is: optExpense1 (where Non-Deductible=1 and Deductible=2)
The combo-box in question is: cboExpense
The table with the combo-source is: lkupExpenseCode
The field I'm populating the combo-box with is: ExpenseCodeID
And the qualifying field is: ExpenseTypeID
The SQL I was using for my control source is as follows:
SELECT lkupExpenseCode.ExpenseCodeID,
FROM lkupExpenseCode
WHERE (((lkupExpenseCode.ExpenseTypeID)=IIf([Forms]![frmExpenseLogEntry]![optExpense1]=1,5,Between 1 and 4)));
but Access keeps changing it and erroring. I think I've figured it out enough to know that this type of statement won't work but I'm a bit stuck for an alternate idea.
Any help would be sincerely appreciated.
~Chad
I want to have a Combo Box on a form, that derives its values from a Table, but does not send selected values to another table. I want to use the value selected in the combo box in VBA code.I have set up a combo box, without a control source, and it shows the values in the drop down list but after I select one the box just goes empty. I expected that after selecting a value that value would be displayed in the combo box (and that would then become the value of the combo box that I could then use in code. I.e, CmdBox1.Value). If so, what do I need to do so that the value selected is displayed (and becomes the value of the combo box?).
View 2 Replies View RelatedI have a combobox containing the "Field Names" of a table called "Main" Also, I have a report called xyz which only has one field.
All I want, whatever field name user selects in the combo, report displays the results of selected field name only.
example
Main Table has three fields (Fie1, Fie2 and Fie3) The combo box lists the names of all three fields (Fie1 to Fie3) Report has a textbox in the detailed section.
I want user to select the field name from combo click button to generate the report (lets say if user selected Fie2) and want textbox to list all the records of Fie2
i tried [Forms]![Fm1]! [cm1] but it is listing the field names not the values in the field...
I have a combo box control in my form named PayDateID, but I told that control to DISPLAY the associated field PayDate.
I now am trying to make a separate control in the same form whose control source is the DISPLAYED PayDate, but it only wants to reference the underlying PayDateID.
How can I make the control source be the displayed value in that combo box?
I've designed a data entry form based on a table. I use a few combo boxes, (linked via SQL statements for their row/source) to fill most of the fields in the table.
What I want to do is populate one textbox on the form with the contents of a field in one of the combo box's row/source tables. The field I want isn't shown in the combo box.
Basically, what I want is that when I choose a PART NUMBER from a combo box, I want the OEM_ID from the same table to jump into the textbox below it.
I think I may have tied myself in knots though to the point where what I want can't be done. Any ideas? I know this is probably going to take a couple of goes at explaining. :P
i have a list box with 6 columns. in column 6 there is a time field. on selecting this list box i want to set the value of a combo box to the time field.
so in the control source i put
[Forms]![frmAppointmentTreatmentItems]![comboTreatmentDuration]
This has no effect. I am not sure if i have done this correctly. but the combo box has manual values in it. the reason i want to have values in it is so if the treatment is 45 minutes at default. But i want to change it to 30 minutes just for a single occasion then i thought that this would be the way to do it.
I am programming a database for a reorganized group in my office. For whatever reason they want their correspondence letters within the Database instead of in Word templates. This way I can standardize their headings and automatically import data from their tables onto their letters.
For this reason I chose a form. I felt using a report wouldn't give them enough editing power. Almost all of the fields on this form are unbound and set to default values. I want to use a combo box so the worker can select the "letter type" they want from the drop down and it will automatically update the body of the letter with the standard format of that type of letter.
Just one problem--these letters are wordy, so the "content" field had to be a memo. I know you can't set memo fields to combo box columns, so I'm in a jam.
how I could populate this memo field into the unbound field using some kind of selector? It doesn't necessarily have to be a combo box.
I want to populate the memo field into the unbound box, but I don't want it to establish a link or control source because I don't want the worker modifying the original record (think of it as a template).
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 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
I have a set of cascading combo boxes. When a value is selected in Cat 1 ID, it narrows down the selections in Cat 2 ID which narrows down the selections in Cat 3 ID.
However, the values don't show up for columns Cat 2 ID or Cat 3 ID until I enter the row. Then I have it set to requery the fields because the underlying data isn't populated. But then when I leave the row, the values go away.
Why doesn't it show the underlying value from the table? Each column is bound to a field in a table - I would assume that the recorded value would show up. Did I miss something easy?
I've attached a picture of exactly what I mean.
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