I'm creating a database for work, and one of my forms currently has 8 subforms on it. For each individual account there can be one, none, or many of any combination of these subforms that will have information on them. I need the form to be able to show all of them, as someone else will be inputting all the information when the database is complete, but to make it easier I have set it up with checkboxes so that when a checkbox is checked, a subform shows, if not checked it doesn't show. I have very limited skills with VBA, so that in itself was an accomplishment for me.
My questions is: is there anyway in access to do it so that if a subform doesn't show, all the ones beneath it that do show will get bumped up so that it is easier to read/input data?
I have attached my DB and the appropriate form is "ICinfoT."
Issue with Cursor moving on multi Subforms in a form.
I have 2 sub-forms in a main form as per screenshot. So Cursor moving from main form to 1st subform working well. After can't move the cursor from 1st subform to 2nd subform with tabbing or entering. Still there in 1st Subform in the last entered filed after tabbing or entering. I've checked on tab orders also they shown correct but it is can't move to 2nd subform.
In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'
In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.
I'm using I have a secondary control being populated by an entry from another control. I was able to find the code I needed to do this using a combo box here in this forum.
me.txtDependent=me.cboPrimary.column(#) (specifics changed to protect my company's paranoid view of privacy)
the code itself was showing up in the secondary control instead of any value. I found though that moving the code from the On Change event for the primary control to the On Current event for the form itself everything is working beautifully.
However, I have a different task now that I need to be able to accomplish with the same two tables but in the opposite direction. I suspect that I'm going to need to create a second set of controls and maybe even a second relationship or table to do this. To use a set of specifics that I think will get this point across, if I had a master table which includes a field for "City" and a secondary table, CityState, which contains both "City" and "State", and two controls, a combo box cboCityState to look up the specific City and a text box, txtState to display the State, putting the following code in the City combo box's On Change event populates a text box with the State when the specific City is selected, or putting it in the form's On Current event will be sure that State shows up in all the copies of a form when the form is used in a search and the City is not changed :
me.txtState=me.cboCityState.column(2)
What I need to do now is to set up a control which allows me to type in the State and have the results show all records that include that State.
I'm using this in a Filter by Form environment. I know the objections to this method and the benefits to creating a dedicated search form, but this method appears to be working well in all other ways for us for now. I have several situations within this form where I have one control showing up at the initial load of the form and at the beginning of the filtering process, allowing selection of multiple values from a long list of values in a list box, for instance, (using the On Filter event) and other controls showing up as a result of the filtering process, to display the selected criteria only, for instance (using the On Apply Filter event), so I'm aware of how to make this work if that's required. I'm thinking I may need to create a second control, something like txtStateInput, to accept the State search criteria and another, something like txtCityResults, to show the cities that are part of the resultant records.
- text box for user to enter EMPID, txtEMPID - LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field... - if the user enters an EMPID that is in the TABLE...display "Y" - if the user enters an EMPID that is NOT in the table...display "N" - a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
Can you update a text box on a form based on input of a date control? Here is what I have. I have several date controls on my form, and I want to update a status textbox based on which of these date boxes is filled out, but I need the status to be filled into the table as well. Do I update the field on the table, and then requery the text box? Is there some other method for accomplishing this?
I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query? So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE. So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?
Hi there. I need a way to pass data between forms, and subforms. I have "form1" which simply contains a text box requiring the user to input an ID which I will call "PID". it also has a button which has the following code:
Private Sub Command5_Click() On Error GoTo Err_Command5_Click
Dim stDocName As String Dim stLinkCriteria As String stLinkCriteria = "[PID]=" & Me.PID stDocName = "TestExam" DoCmd.OpenForm stDocName, , , stLinkCriteria
On the form testExam, I have a page control with 3 separate tabs. For each tab, I've embedded three separate subforms. All forms involved, including the container form (testExam) are bound to separate tables. All tables have one common field - PID I'd like the user to only have to input the PID on one form, and have it trickle down to all subforms.
In the FOrm_Current sub of the testExam form, I have the following code:
Private Sub Form_Current() [PID] = Forms!frmForm1.PID End Sub
Using this code, I am able to pass the data from Form1 to testExam, have the PID on testExam filled in and on tab 2. But tab 1 will not populate automatically. I have no idea why. I don't know where to start troubleshooting this. Please help.
Is this even a good way to pass data between subforms?
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
I have a main form with several subforms within it. These subforms are navigation tabs. In the footer of the main form I have a textbox that I want to hide unless the 2nd tab is selected.
Details:
3 Tabs, 1 subnavigation subform, 3 tab indexes(0,1,2) tab index 1 the textbox on the main form(parent) should be visible. tab index 0 and 2 the textbox should be hidden.
I was thinking I could do something like when tab index 1 is onFocus then textbox = visible and vice versa on the other tab indexes?
Right slightly complicated (in my eyes) and I have not found anything in previous threads to help but here goes:
I have a main form (FRM_SUMMARYREVIEW) that has an unbound subform field on it (subfrmmaster).
When FRM_SUMMARYREVIEW is first opened subfrmmaster has its sourceobject set to a form called FRM_OPENSUMMARY.
This subform lists all my open escalations, in a continuous forms format and has a field in it called "escalationid" and when I click this field "subfrmmaster" changes it's sourceobject to another form called FRM_MAINDATA and should display the data relevant to the escalationid I have just selected.
In the past I used to click on "escalationid" and it opened up FRM_MAINDATA as a new form, using the following code:
However how can I get the subfrmmaster sourceobject to change and display the relevant escalation detail ?
My other option is to have two subforms on the main form, one with "FRM_OPENSUMMARY" the other with "FRM_MAINDATA", as "FRM_OPENSUMMARY" has a list of escalations displayed, in a continuous forms format could I set it so as I arrow down the list, the data in FRM_MAINDATA displays the highlighted escalation in "FRM_OPENSUMMARY".
I have created a form with 3 subforms on. i was just wondering is it possible to display/ hide these subforms based on a Yes/No field in the form. as the subforms would only be valid if the field is ticked as yes.
Sub Service ID (primary key) Sub Service Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
I have a database that has several tables, each table has account numbers in them. I also have queries set for each table to bring in desired information. Is it possible to set up a form so the user can input an account number and depending on which table the account number is in, that query will run?
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.
Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.
So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.
Example:Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.
I need to make a query work so that when someone enters info into a master form, it will pull the data for that job onto other forms when the job # is entered. Thanks for any help.
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE") sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;" DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String SampleID = InputBox("Enter Sample ID") If SampleID > 0 Then DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID Else DoCmd.Close End If End Sub
I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
I have set up my main data entry form as a navigation form with 5 tabs/pages. The first three all contain fields from my main data table, the other 2 tabs/pages have fields from related tables.
Ok, so my question is: is if there is a way to go from tab/page 1 through the other 4 in succession with the main primary key (in this case an object ID number) automatically populated through. As it is now, when I start a new record for an object on tab/page 1, and then go to the next tab/page, it registers a new record has been created (at the bottom of the form it updates the total amount of records entered), but I have to manually enter in the ID field at the top the object ID number that I am entering info for. Otherwise it is still showing the details associated with the last record I was in the form updating.
