Forms :: Asking For Sub-datasheet After Adding Extra Relationship
Jun 20, 2013
I have 2 relationships between 3 tables,
Customer Table > Customer ID (pk) 1
Issues Table > Customer ID (FK) Many
Complaints Table > Customer ID (FK) Many
Each table has Customer ID as you can see, I have linked the Customer ID From Issues & Complaints table to the Customer ID field to the Customer Table using a 1 to Many relationship.
Am creating a form on the customers table so an admin can add a customer issue against the customer this is working fine, The trouble is when I link the Complaints table as well. its asking for subdatasheets.
I have two tables containing data from two separate sources. The unique "link" between the two is a JobNumber field. I need to set up a relationship between the two tables using the JobNumber field.
Problem:
In one table, the JobNumber has been input in this format: 12-00345-01 In 2nd table, the JobNumber has been input in this format: 12-00345-1
How can I tie these when the 2nd table is missing the "0"?
I have 120 tables, each with the same name except 2 identifying characters at the end eg pc_dist_ab, pc_dist_al Each table currentnly has 3 columns. I would like to be able to add 2 additonal columns to each table with one query and was hoping an alter table query where the table name matches pattern would have worked but evidently not.
Is there a way to build some sort of dynamic query to add extra columns to these different tables at the same time?
To save another post I guess Once this is done I would then like to create 1 main table by creating a new table and appending all the files together- again I would prefer to be able to run this once.
I'd appreciate any help/thoughts as to whether this can be done?:confused: Thanks
I am trying to add extra validation in a sample db (done in A97 (converted to A2003)see attached.
I am attempting the following: Please not that the db has existing validation which I would like to keep (or change if advised) ...
Conditions: 1. Input into field "ID" must start with the letter P or p or R or r 2. If field "ID" starts with the letter P or p and field "Code" starts with the letter R or r, then a message stating that this combination is invalid should appear 3. If field "ID" starts with the letter R or r and field "Code" starts with the letter P or p, then a message stating that this combination is invalid should appear.
I am working with Independent School Districts (ISD) in the state of Texas. The State is divided into 20 regions. Over 1000 ISDs are divided among those 20 regions. Each of the 1000+ ISDs has between 3 and several hundred individual school campuses. Each has a name, but as you would imagine, there is some duplication - not within an ISD, but between different ISDs. For instance, both Dallas ISD and San Antonio ISD may have a Lyndon B. Johnson campus.
We are an association that has members on most of the campuses.
I am using an autonumber ID as primary key on the member, campus, and ISD tables.
During entry/editing within the member table using the basic datasheet, I want to use a lookup field (showing campus names) to select the campus. That is not a problem by itself. But with many tens of thousands of potential campuses to choose from that doesn't work very well. What I would like to do is restrict the campus lookup to the names within the ISD, that is, filter the lookup by ISD.
However, I don't want to include the ISD in the member records because that will not be full normalization, but I am willing to do so if necessary. But if I do, I want to include only the ISD number (another autonumber primary key for the ISD table). So during member entry, I want to translate the ISD key into the related ISD name for selecting the correct ISD, before I try to select the correct campus within the ISD.
Essentially what I want to do, is member entry thru a datasheet and first look up the ISD to find it by name, then use that selection to filter the lookup on campus to limit the selections to only campus names within the chosen ISD.
I would be grateful for any assistance with this. I am not married to this approach, so if someone has a better but different idea, please suggest it. However, I am a relative novice, so bear that in mind.
When I open my access 2007 database from the switchboard, the form that it opens up to is a parent form with a subform embedded in it. The subform is linked by the 'org name' and by 'year'.
I am finding that when I open the parent form,there is always an additional empty record in the subform, alongside the record which has data in it.
I am not sure why it is doing this, especially as when you enter another record, this empty form vanishes. Its almost acting as the default form?
I have also noticed that when I go to another form which is displaying specific data linked to the 'org name' and 'year' it also has an extra record
However, the weird thing is it does not show up in the table. And again, once you go to a new organisation and input some data, and then go back to the other organisation,the additional record has gone?
Is there a simple property setting whereas each time you open the form, it always opens up to where you essentially left off, no empty record?
I have a continuous subform with allow additions set to false. To make a new record I have used some update vba to create the record direct in the underlying query, then requery the form and the partly created record appears. The user then adds a quantity and some text. The subform still appears without the new record line.....However if I click the button again to create a second new record I end up getting an extra 2 lines.
One is a duplicate of my previous one and a new blank record. These do not actually appear in the underlying table and the subform looks ok. However this extra record confuses the end user and I want to avoid it. Refresh or shift f9 does not eleviate the problem. Sometimes I even get two "current record" pointers.
I always have a problem with input forms in Access in that I usually end up with an empty excess record which is being created because I do not know how to program this correctly.
I have a Purchase Order Receipt form (frmPurchase). When I receive a certain quantity of an article associated with a lot number (or a serial number) I have another input form opening up (frmLotorSerial), passing to it through Openargs, the article and the quantity received.
Say we receive 10 units of which we want to register the serial number, I want the user to be able to enter a maximum of 10 units (could be 10 records if each record = 1 serial number, but it could be less than 10 if we have several units per lot number), and not one more, into this frmLotorSerial.
The record looks like this: -key -artno (article) -recqty (quantity received) -date -warehouse -lot number
I used the lost_focus event on lot number to check whether the total received quantity in this form matches the total passed to it from the Purchase form, and if it does to stop the input. Since I could not close the window in the code of this event (error 2585) I moved the close command to the current event of the form.
Doing this closes the form all right, but it always creates one record extra, which of course does not carry any information, apart from the date which has a default value of now.
How should I program/what events should I use to: -test that the user should not go any further (total received in frmLotorSerial = total received in frmPurchase) -close the window and, above all, not create this extra record.
My form was working fine but when I added an extra tab it started showing as completely blank in design view. When I deleted the tab I had added I still was left with the same problem!
I've attached an image of the form properties. I want to use the form to enter data so it needs to show even if there are no records.
I had read that it could be if there is no data but typing data in the tables hasn't worked either.
There is a requirement for 1)Populating a datasheet in a subform with a querystring which is dynamically built in VBA.This querystring is constructed based on the search criteria fields selected by the user in the main form.How can this be immplemented?
2_To click/double click a record in the datasheet,extract data and populate textfields,comboboxes with it which are in the main form.How is this achieved?Also,I can't find click events in a datasheet.
I've got a Datasheet form which has a Datasheet Subform within it. By default, none of the subforms display, and the user clicks the "plus" on the leftmost column. What I'd like to do is prevent a user from having multiple subforms open at once. Basically, when the user clicks to open another subform, the first one closes.
How can this be done?
Also, is there a way to do conditional formatting (either using the wizard or with vba) on datasheet forms?
I have 2 tables: TblCustomers (one) [field: MblNo; CustName] and TblDetails (many) [field: MblNo; TalkPlan; BillingAddress] with (1 isto many) relationship. The TblCustomers has primary key [MblNo] that is foreign key in TblDetails.
They have 2 bound forms frmTblCustomers and frmTblDetails. Now frmTblDetails is inserted as subform in the frmTblCustomers.
I would like to keep on adding [MblNo; CustName] in TblCustomers and customer details in TblDetails (ie Talkplan, etc). Now, if I try to enter an already entered [MblNo] in TblCustomers it will not allow and generate error. But a customer may appear more than once in TblDetails (as he might keep on changing his TalkPlan or BillingAddress).
Any idea on how should the forms be built so that whenever I attempt to add an already entered MblNo in TblCustomers it will alert me that "the MblNo that you are attempting is Duplicate" and the focus of the control will be set to frmTblDetails so that I can contnue adding details in the [TalkPlan]; [BillingAddress]. Any help is appreciated. Thanks. Prodigy...
My form is a datasheet with 2 fields: "Product" and "Quantity". Obviously I can't add a calculated field in this type of form; however I would like the word " Total" to display in the "Quantity" field just under the last record. I would also like the sum of the "Quantity" field to display at the bottom of the "Quantity" field in the empty record below the last record.
I have been fighting a problem with sorting the data in a subform that is in a datasheet. It is sorting the data automatically so the entries are not in the original order. Is there a way to disable the feature so that when I put data into the datasheet it will appear in the same order that it was originally.
Anyway, I have a form that opens in datasheet view. It displays company names that exist on a client table based on a "Like" criteria the user typed into a text box. They type the word "network" in the text box, click search and a datasheet opens that has all companies that have "network" somewhere in the title. That's it. Works perfectly. We'll call this datasheet view form result "Test Query Form."
I have a second form that currently allows users to select a company from a drop down box and then populates the rest of the form with data from a table for that company. Again, works perfectly. We'll call this form "Existing Info."
I want to make a change to this second form. Rather than allow a drop down combo box for selection of the company, I want the "Test Query Form" to open the "Existing Info" form and automatically populate the form based on the company that the user double clicks on in the "Test Query Form." For instance, if I type in "network" and clicked search and got three companies with the word "network" in the company name, I want the user to double click on the second company name and have the "Existing Info" form open up with that second company on it and all the info populated for that second company.
I know this needs to be VBA code on the dblClick command for the company name cell on the "Test Query Form" but I can't quite get it to push the company name through to the "Existing Info" form.
my requirement was for a control in a subform and I needed all this to affect a first column freeze.The code was in the Onload for the main form.
Code:
Dim ctl As Control Set ctl = Forms("frmPlanner").Controls("subfrmTempCtrl") Me.SetFocus ctl.SetFocus ctl.Form.Controls(0).SetFocus DoCmd.RunCommand acCmdFreezeColumn
It does work but I can't fathom why I need more than the last .SetFocus. Leave any of the prior ones out and it gives an error to the effect that can't do it in those circumstances. I understand the control has to have focus for it to work but why are the other's required?
I'm trying to develop a werehouse database for me.and I'm in a terrible dead end.Formely I have a table Articles where I have all the articles on the market with the description, EAN, price. and a table Movement where with a code (related with the article's table) I can store all the item I fisically have in my werehouse and items I sell. (with a boolen field that set if the item is an income or an outcome).
I have a form linked to the Movement Table.I'd like to write an EAN in a textbox query that EAN to the article table and output the results in a datasheet.
I have been handed an Access database (Access 2002-2003 file format) to clean up and update as necessary. Currently I am trying to add new rows to a datasheet that is in a subform. I have had no trouble in the past adding rows and for some reason now I can't.
In some instances I can update an existing row but never add a new one. Also, the records options are greyed out in the ribbon except for a select few that are not useful (refresh all, totals, more). Can't add new record from their either. I am in the main form that contins the subform which contains the datasheet, in form view.
I have made an access database for doing inventory at work. We have an inventory of approx. 700 items that need to be counted each month. I have a subform that is in datasheet view, it has Inventory IDs, product descriptions, UOMs, and a field to enter quantities. On the parent form are pictures and extra descriptions linked to each item in the subform.
The Inventory IDs all start with a 3 letter alpha code for which class of item they are PLU for plumbing, FAS for fasteners, ELD for electrical device, etc. There are about 15 different classes of these inventory items. The rest of the InvIDs are 4 digits: (PLU5453 or ELD1123)
We are using tablets to do the inventory counts, so to search for an item we can swipe through the datasheet to find the InvIDs we are looking for and then enter the quantity. After our initial inventory my supervisor asked if I could add buttons to jump to the first item in a particular class of item such as PLU0001. He showed me his address book in his phone as an example, how he can push a P button to bring him to the first name that starts with P.
I considered tabs for each class, but he wants to navigate to other items by just swiping also. I tried to make a Macro that would use Go To Record but was unable to make that work.
I have recently added a subform to a form and wish to display the subform in Datasheet view. Whilst I can get the proper data and headings to display, I cannot figure out how to get the column widths to persist. I can adjust the column widths while in (main) form view right on the subfirm columns themselves but after I save and reload the main & subform form, the widths revert to some sort of default value.
I have also tried adjusting the columns in the subform's associated data table itself as well as adjusting the text box widths when the subform is displayed in Form view but it does not affect the column widths when the subform displays Datasheet view.
What is more annoying is that I have another subform in which I was able to make the Datasheet view column widths persist but I don't seem to be able to properly recall how I did it.
Sum Function in Unbound text on Datasheet subform..How to make a sum formula without count orange color rows Amount. Because that orange color rows is product return items so no need to count in SubtotalRs. Actually I have used below normal sum formula so it is ok but need to change the formula. how to give a formula without count orange Amounts?
I'm using the Search form that's provided in the code repository; it's working fine. I am trying to transition from a listbox to a datasheet subform instead for a little more flexibility in the formatting.
I see that .itemdata is for listboxes, but what is the datasheet equivalent? Tried searching, but didn't find anything.
Here's the section of code:
Private Sub Form_Load() DoCmd.Maximize Me.SearchResults = Me.SearchResults.ItemData(1) Me.SearchFor.SetFocus End Sub
I am new to access web databases (access 2010). I have a "web datasheet form" that shows a query of all the projects in my database (fields are ProjectID, ProjectName, Assignedto)
The assignedTo field has been populated with the sharepoint user name.I now want to automatically filter this form to only show the projects for the currentwebUser? ie. I don't want the user to have to select their name from a dropdown.Do I do it on the query or on the from that loads.?
I have a form that shows details of a record. I have a command button to open associated records in another form in DS view. My code will open the DS and show all records or filtered to specific record.
What I would like to do is use code on command button to open DS view and simply highlight (find) the record that was being viewed in the first form.
Main Form = frmBroachBase DS Form = frmBroachBaseDS
Associated fields on both forms are - [AutoID], [BroachID] - that can be used to link the two forms.
