Hi,
I wonder if it's possible to bind one form to more than one table without using 'sub-form'?
I basically have one text box that I want to bind to a different table than the one the form is linked to and I don't know if I should use a subform.
Regards,
B
I have a user form that has many unbound controls that are used as calculated controls that lead up to an unbound control that is the grand total of all the data entered. I would like for the unbound grand total control to update the grand total field in the table where I would like to store the data..is there a way I can bind an unbound control to a field in a table?
View 1 Replies View RelatedBasically, I originally wanted to create a form which I can input data into Access with by using a button (I was unable to get Access to append the information from the forms to the table, so it didn't work out!). I did not want the fields on the form to be linked to a table, as then a record (and most importantly an auto-number) is created as soon as somebody starts typing. Should somebody stop typing halfway through and quit the form an autonumber will have been generated, which makes the number of "users" seem higher than it actually is. I got around this by changing the field from autonumber to number and then creating a query that selects the maximum value of ID in the table, then adds 1 to it (which is essentially the lowest unique number. I tend to call this newID).
The problem I now have is setting this to be written to the table alongside the data from the form (the rest of the fields on the form are now connected to the table, as I am no longer using an autonumber). I have tried the following: Setting the form to run the expression "[ID] = [qryMaximumUserID]![NewID] " on load. This returns the error "The object doesn't contain the Automation object 'qryMaximumUserID.' Setting the control source of the text box to be dLookup. This fills the textbox with the correct value, but then it doesn't write it to the table! Setting the default value of the field to be 0, then running an update query to update any ID of 0 (criteria "0" to the value of newID "update to: [qryMaximumUserID].[NewID]. "). However this doesn't work as whenever the query is run it asks for a parameter to be entered, rather than just taking the value from the other query. Writing a macro that is run on load to SetValue of item:[ID] to expression: [Forms]![qryMaximumUserID]![NewID]. However this returns an error "Microsoft Access cannot find the referenced form 'qryMaximumUserID' you entered in the expression.
I have a form with a (large) number of controls which acts as a kind of read-only dashboard of data. The data is split across a number of tables (4, for now, but may grow) I've created a query which amalgamates all of the data from the tables into a single dataset and set that as the RecordSource (Snapshot) of the form. I now want to bind each of the controls to a particular field returned by that query.
However - I have a combobox (same form) which allows users to select a date and I need the controls to update with the relevant data for that date whenever the combobox is changed. The query already returns values for each field over a range of dates so the data is available within the form's RecordSource - I just don't know how to include the date criteria, as specified by the combobox, when binding each individual control?
I am a girl guide leader trying to make a faster way to keep track of my unit's completed badge and program work. I have made tables for every area I need (I started with one table but it was too large to change into a form).
In total I have:
- Basic Member table (main information like their name and how many years they have been in, whether they are still in the unit, etc)
- Program area tables
- Interest badge tables
What I want to do is be able to "add/edit" members from the main form, then go to all the sub forms for their program and badge work -- problem is, how to link them!
I am using Access 2007-2010.
I would like to create a text box where I can input todays date, then I want to be able to select the tables I want to update with that information.
So if I input todays date I can then update my Stock, Sales and Control tables with todays date. Tomorrow I would only like to update the Control and Tape tables with it as its the weekend.
I been playing around with 'new date for next record' scenario but the problem is that on the weekend we would not input any data into some of the tables.
Hope I've explained myself clear enough.
Thanks
Tim
Hi!,
I am interested in making a form based on 2 tables. I would like to put in ID and wish that the name and contact information is automatically filled using the ID match from a second table. Then I proceed with the data entry which is saved in the first table. How do I do the autofill based on the ID check. Do I need some code for that?
Thanks.
I currently have 1 form linked to 1 table which data from another table is appended to for editing, re-appended to original table and then rows are deleted. However, this process will need to be done by 4 other users soon and I needed to know if I need to create 4 separate tables and 4 separate forms or is there another way to implement this? The append query has clauses that will display only a specific user data for editing, however, I do believe that if 1 user is in the editing table, then when 1 of the other 4 users attempt to run the query to append their data from the original table, they will get an error message that this table is already in use. I just need to know if there is a way to set up an alternative to re-creating 4 tables/4 forms again.
View 4 Replies View RelatedI am trying to create a textbox control that uses an expression. I then want this expression to be stored in a field in a table I have created. Can someone help me with this please?
tsg
I have a form. The form is bound to an underlying query which is a left join between two tables. (SELECT ... T1 LEFT JOIN T2 ON T1.RID = T2.RID ....)
Now, what I want to happen is the value in T1.RID does have a match in T2, then I want a sequence of text controls to be bound to fields from T2, otherwise I want them to be bound to fields in T1.
I almost have it working. I have Form_Current() and T1_RID_Change() both calling a routine
sub BindControls()
if isnull(T2_RID.value) then
txtField1.Controlsource = "T1.Field1"
txtField2.Controlsource = "T1.Field2"
' etc for each field
else
txtField1.ControlSource = "T2.Field1"
txtField2.ControlSource = "T2.Field2"
' etc for each field
end if
end sub
The problem is that when I change the value in T1.RID, most of the times it does change the controlsource appropriately. But other times it lags. It keeps the wrong controlsource until I try changin RID again, and sometimes the text boxes are just blank, so I am not sure what is going.
Am I ultimately going to be able to make this scenario work?
Thanks,
David
Could you please help me out.
I am trying to develop a DB that is multi user. i ahve not split the data from the forms into separate db's as the users are in different locations and our network can be very slow.
part of the requirerment is to produce a drawing approval form, my current sticking point is that the method i am currently using to create the forms only allows one person at a time to use the process.
currently i have a command button that runs a query and then creates a table from the results. the table is then used as the data source for a mail merged form in word.
what i ultimatly need is a process which allows multiple users to generate these forms at the same time and have the ability to save the form (with no code behind it) as a word document (either RTF etc)
i am thinking that if i create a query on the fly in VB and then use the results to use in a report this would allow me to do the above.....
So my questions are:
1) is my assumption correct
2) If so, how do i do it as i have never done it before
3) If the above is not the best solution/ does not work... what will/is
I have a report that shows 3 pivot tables on the same page. What we would like to do is total (or perhaps subtotal) the three tables into a summary table.
each table has number code, name, data 1, data 2.
The three tables are identical with the exception of I filter on only specific number codes.
Currently I simply have a 4th pivot table with all the number codes and just hide all the rows except top and totals.
I would love to be able to use something like subtotal to simply display the totals (from my 3 tables)
I am trying to create an input form for input to tables.There is a main TblContacts table with fields such as: LastName, FirstName, Address, etc. There are also ID fields for the Company and Country and joins to the Company and Country tables, each with just ID and name fields.I want to have all the fields from the TblContacts table and the Name fields from the Company and Country tables on the form. I want to be able to select the Name fields from the Company and Country tables from combo boxes and for these also to populate the Company and Country ID fields (from the TblContacts table).
To create the form, I selected Create Form whilst the TblContacts was open. Then, I added the combo boxes pulling the names from the Company and Country tables.My problem is that the selection of the Company and Country names above is NOT populating the Company and Country ID fields on the form (taken from the TblContacts table. I have checked in relationships that the joins have been created.
I want to create a form that has several parts(5), that will direct to 5 different tables(they will all be connected). I want to keep it as one form because if I'm successful it will be easier for people to transition from a paper to electronic form(if the format is similar). I don't want any subforms, I just want to be able to direct the information to different tables.
View 1 Replies View RelatedI have data that needs to be entered. It is the same data across 3 tables but only for the first three columns, then it is different. I can create a form for one of these but not for all three.
View 4 Replies View RelatedI am currently working on a form that involves several linked tables. Since our personnel got so used to a certain form format, I have scanned the form and imported into an access form as a background and have laid corresponding fields on top.(I have attached a snapshot of the form). This works great for a view only form, but I can't seem to add/edit records using this form because of the table relationship. I have done some research, and found that one way to get around this problem is to use subforms, but that would kill the form format. Another way I have seen (which I am not sure how to do) is to have a temporary field and once you hit the submit button it will then fill out the tables.
View 1 Replies View RelatedI am fairly new to Access and am having some trouble creating a search form. I have numerous tables, which contain information regarding clients and contracts, that are all connected by relationships. I would like to create a form in which I can type in the name of the client and pull up all the information regarding it from the various tables (i.e. when it was signed, the amount, and etc).
View 2 Replies View Relatedproblem is that i have 2 tables - Consolidated Orders, Customers.
Customers has 2 fields with a record inside one field with the clients name , but it also has an address in another field in the same table which i want to link to the name of the client because in my invoice i show the companies name and to the left of it i show their address.
i wish to link the two fields together so that when the name shows up, the address of the client will be to the left side .
Hello Everyone,
I am stuck in a problem for many days now.
In the form, on which I am working, there are 2 dropdown lists (combo box).
The data in the first combo is coming from a list and in the second combo, I am running a query, and depending upon the selection in the first combo, the data in the second combo may come from one of many tables in the database.
What I want is to have control over the data, before it is being displayed in the second combo, as the data in have many null and 'same name' entries. But what I don't want is to change the original data in the tables.
Till now, I have been trying to make use of persistant recordset, that is, by storing the recordset in an external table, changing its data and binding it to the second combo. But I am unable to bind the data.
What in your opinion, is the best approach to accomplish this. A snippet will be of great help as I am not a experienced programmer.
regards
Hy,i have some ADO recordset and i want to bind it to blank report that I made in reports.How to do this.
If i create report in reports section and after that i want to see it in my code as that same object and set his record or data source property how to do this??
I know that you can set the recordsource property directly in report but it includes some values from textbox in my form that doesn't show corectly(it's an array of numbers,and i don't know why it doesn't accept it).
Thanks
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records
Nestling Banding Records
Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
I'm working to create a staffing database that houses changes to staffing week over week.
I have one primary table, the "empMaster" table, that stores the employee's name, contact information, etc. I have other individual tables for noting which employee reports to which manager, what their business title is, what group they're in, their training history, etc.
Once I've populated the empMaster table with employee information, I want a form that allows me to update each of the other tables IF there as a change. Some weeks will have a manager change, some only a business title change, some a group change, some a training change, some all of the above. The problem I run into is that I will sometimes process hundreds of changes a week, sometimes only 10-20 so I almost have to use datasheet view for mass edits. I'm relatively new to Access and I'm having a hard time getting my form to allow this level of flexibility and to update all fields needed.
When I build a form that includes more than two tables (let's say I want to update a Manager and a Business Title), the form will not work and populates nothing. I believe it's because Access wants there to be an existing record to match to across all three tables and there will not always be.
How do you create a form that is not tied to one single record source? In other words, I want to be able to select the record source that it updates. I have a bunch of tables that have the same data structure but are separated due to geographical nature among other reasons. Is there a way to do this?
View 10 Replies View RelatedFunction that searches modules for instances of early binding? Im sure i could write one, but why reinvent the wheel?
View 2 Replies View RelatedI currently have a pharmaceutical lot database set up in the following format:
MFGData (table w/Manufacturing Info)
QAData (table w/ Quality Assurance Info)
QCData (table w/ Quality Control Info)
PASData (table w/ Process & Analytical Science Info)
SCData (table w/ Supply Chain Info)
[Code] ....
Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).
I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part.
To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.
If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?
My database allows you to log issues (see attached)
An issue will belong to the project, the project may have multiple test plans.
The issue will also be the primary responsibility of a company, and that company may have multiple contacts (people)
Now, i could create a form over the top of each table but then if someone wanted to add a new project, testplans for that project, suppliers (companies) for that project AND new contacts for those companies, they are going to end up going through loads, and loads of forms.
So my question is, what approach would you recommend to create a control panel where they could add these items with the fewest number of page switches possible? (preferably none.