Access - New Record In Non-linked Subform
Nov 12, 2012
I have an access form that has a marco button to open another form using a filter that returns records in the new form that have a matching recordID field from the main form. This works fine.
I want to be able to add records to the table which works as well only the recordID field is not populated with the id field used in the filter from the main form. How can I populate the recordID field with that of the master filed from the main form? It is just blank on the new records created now.
View Replies
ADVERTISEMENT
Apr 12, 2014
I am trying to create a database to manage IT assets, most of the structure is done, I'm now trying to get the details done.
The issue I am currently trying to tackle is to get a control button on one form to create a new record in the sub form that sits in the main form via another pop-up form.
So I have :
>PeripheralsViewForm - that has 4 control buttons (Edit Quantity, Edit Description,Add an Item and Exit)
>> PeripheralsSubForm - (Datasheet) which is referenced to PeripheralsQuery
The Edit Quantity and Edit Description open a separate form which allows the user to update only the quantity or description respectively (I have done it this way rather than allowing edits in the data sheet as I feel that it is too easy to hit a key incorrectly and overwrite data in the datasheet view) Both of these functions work perfectly (to my amazement) - I am using the id field in the subform to link to the pop-up form which then has unbound text input boxes, which I then write back to the subform.
e.g from edit button on click event: DoCmd.OpenForm "perquantform", , , " ID=" & Form.PeripheralsSubForm!ID and then from the pop-up form "perquantform" on the "Exit & Save" button I have :
Forms!peripheralsViewForm.PeripheralsSubForm.Form! Quantity = Me.newquant DoCmd.Close acForm, "perquantform", acSaveYes
This allows the user to select the record in the datasheet form and then click either the change quantity or description button.Also also the other reason I did it this way was because I just couldn't get the subform to requery when I had the two edit popups linked to the table or query.
The problem now is that when I click on the "Add an Item" button, it overwrites whatever record is currently selected !
The code I have is this:
From the "Add an Item" button on click action:
DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form! , acNewRec
DoCmd.OpenForm "addperform"
And from the form "addperform" - "Save & Exit" button on click action:
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerType = Me.pertypedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerMake = Me.permakedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerModel = Me.newmodel
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerDescription = Me.newdescription
Forms!peripheralsViewForm.PeripheralsSubForm.Form! Quantity = Me.newquantity
DoCmd.OpenForm "PeripheralsViewForm"
DoCmd.Close acForm, "addperform"
--------------------------------------
the line "DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form! , acNewRec" is what I have added to try to solve the overwrite issue, but when I run this it comes up with the error :
"runtime error 2498 - An expression you entered is the wrong data type for one of the arguments"
I have put this line in the "addperform" exit action and it comes up with the same sort of error.
Using Access 2010.
View 14 Replies
View Related
Jan 22, 2014
I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code:
Private Sub Accounting_Unit_Enter()
'find where AUs match. only works for first Subform2 record
If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then
msgbox "Match"
End If
End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
View 6 Replies
View Related
Jun 16, 2006
my form works fine on the first record of the database but now i have added a 2nd record some of the functionallity form the first record is applied to the second. At the moment in my table for each record i have a status field which speifies whether certain data is visible to the user or not. When i select visible = false on record one this is also appled to record 2. I dont want this and want each record to have there own specific status.
For example at the moment i have:
if me.combobox.value = 1
Me.mytxtbox.visible=true
else
Me.mytxtbox.visible=false
endif
This seems to apply the rule to all records not just the current one i am in. If explaination isnt clear i will gladly expand further
View 2 Replies
View Related
Apr 6, 2005
I have a form that I have linked (master/child) to a combo box on my main form. I have a requery command for the subform set to the "After Update" event on the combo box. When the combo box is updated originally, the subform updates. However, if the combo box is updated after having a value, the subform does not update.
First off, is the requery command even the right command to use to update the subform? (it's source object is a table) Second, is there a reason it wouldn't work if that is the correct command?
Any ideas?
View 2 Replies
View Related
Jan 26, 2006
Hi all,
I am experiencing difficulties with a subform in a database I am designing for a dance group.
First, a little background about the database. There are 2 main parts:
a) a library of CD information and dance information as well as which dances can be done to which songs
b) a tool to plan monthly dance events
Now for my problem:
I have a main form that's built on the PlanProgram table (see attached relationship diagram) and then a subform that runs from the PlanProgramDetail table. Everything works OK up to that point. However, when I go into the subform's record source and add the table Lessons - this is where it "breaks". What happens is when I go back out to the main form and go to enter data in the subform the child field does not behave normally. It doesn't automatically enter the main form's recno_plnprgm number into the subform's field of the same name. Prior to adding the table called Lessons to the subform it did automatically insert the recno_plnprgm number from the main form to the subform.
The reason I want to add Lessons to the subform is so I can update that table's information as well.
Any "fix-it" ideas would be greatly appreciated.
Thanks, Heather
View 8 Replies
View Related
Dec 3, 2005
I have linked a SQL table to my ms access database. In order to view the SQL table as read-only and still be able to update the access table I have setup a form with a subform.
I know it would be a dublication of data but is there a way thru code of copying the data on the subform to my access table. I was hoping I could put a button on the form that would copy this data. My idea is after the copy is done all the information I need will be in my access table. I'm new to this linking idea and am worried I will only have half the information I need if the link breaks etc.
Thanks
View 1 Replies
View Related
Oct 4, 2006
Hi,
Overview:
Ok so i have document database, and i have a number of forms these forms have a tab control layout on them. There is a 'Search' tab, a 'Add' tab, and a 'Edit' tab. Now what i have is a edit log table linked with the documents table on two seperate subforms on the 'Search' page. They are linked by the 'Document Number' which is not the primary key. The 'Add' page is linked to the document subform and the 'Edit' page is linked to the Log subform.
Aim:
My aim is to have a changes log for each document so that when i select a document in the document subform that the all the changes made to that document are shown on the log subform.
Problem:
When i open the 'Search' tab there are somtimes more than one record with the same 'document number' in the log subform. This is normal as users may update the same document more than once causing there to be 2 or more of the same document number listed under the log subform; but... this also causes the document subform to display a duplicate of the document with that 'document number'.
Proposed Solution:
I dont know if there is a way but i was wondering if there is a piece of code that will automaticly hide any records that have the same document number on the document subform. Or if there is a better way please tell me.
Thanks for taking the time to read this, i hope i havent wasted your time by asking a simple and stupid question.
,Leon
View 3 Replies
View Related
Jun 10, 2015
I have a form [Art_DepartmentFilter] that has a subform [Art_ByRoomSubform]. The linked master/child fields are DeptID and RoomID.
There is a list box on the form that allows you to select the department, and this also updates a list box that shows the rooms in that department that have art in them.
I would like the user to be able to filter the form to show either all of the art in that department, or just the art in the specific room. I have two separate buttons, one for each filter (cmdDeptFilter, and cmdRoomFilter).
I realized I had to add the link for the RoomID in order to get the filter for the room to work (which it does nowoCmd.ApplyFilter , "[qry_artbyroom]![DeptID]=[forms]![art_departmentfilter]![textdeptid] and [qry_artbyroom]![roomid]=[forms]![art_departmentfilter]![textroomid]").
However, DoCmd.ApplyFilter , "[qry_artbyroom]![DeptID]=[forms]![art_departmentfilter]![textdeptid] and [qry_artbyroom]![roomid] like '*'" returns just the first room listed for the department, not all of the rooms.
How can I show all of the rooms for the department selected?
View 5 Replies
View Related
Dec 10, 2012
I have a form that users enter billing logs in. There is a button users can click to the right of their billing data that opens another form where they can enter details regarding the billing log. Beside this button is a check box that shows them if details for this particular billing log has been created.
I can not for the life of me work out how to get the check box to update with a check after I enter Billing details without opening and closing the form again. I felt I have tried requery and refresh in all ways I have seen here, yet it is not working.
The main form is frmDslEnter and the billing log is entered in a subform from here called tblSessions subform. The checkbox on this subform is called chkProgressEntered, and it's control source is =Not IsNull([DSLIDNumber]). I tried putting the refresh on the Close button on the Billing Details page, on the Got Focus of the subform and the main form and haven't had any luck.
View 12 Replies
View Related
Feb 9, 2006
Hello in the grand scheme of things i would like to achieve the following,
Have a main form with a continuous sub form[1stSubform] linked to it. I then have a second continuous sub form[2ndsubform] located on the main form, I would like to link the values of [2ndsubform] to the value selected in the continuous [1stsubform].
I can link the two together using following in the masterlink field
[1stSubForm].form![inspectionID]
When i select a record within the first subform nothing happens, however if i move to the next record on the main form the 2ndsubform values do change (to the related value in 1stSubform).
How do i get the 2ndsubform values to be driven by the selected record in the first subform rather than simply its first record?
ps i have tried the following in [1stSubform] on current and it brings up an error
Me.Parent![2ndSubform].Requery
thanks
View 4 Replies
View Related
May 11, 2015
How do i create a report that is linked to my sub-form that i have just sorted in a different form? let's say i have a form named View Records and on that form i have added a subform that is linked to my Employee Table, and then on that View records form i have added a few combo box in order to sort the data on my subform on that form, now what i want to do is that, i want to link those data that i just sorted on my subform and transfer it to a report.
View 5 Replies
View Related
May 10, 2013
I have a form that is based on a linked table of students eligible to complete a online survey. Within that form, I have a sub form (based on another linked table) of students who have completed the form and at what date and time.
Only about 1 in 10 eligible students complete the form.
I'd like to filter by students who have completed the form, and sort by date submitted.
So I have the main form called "students" and the subform with the date submitted is called "New - BasicInfo subform" and they are linked by student ID. The date submitted field in the subform is formatted like "5/6/2013 3:07:32 PM".
I've tried adding this to me "filter" field in the STudents form properties:
Between [Students]![New - BasicInfo subform]![05/06/2013] AND [Students]![New - BasicInfo subform]![10/01/2013]
No luck, I get syntax error missing operator.
View 1 Replies
View Related
Mar 9, 2014
I have two subforms in a main form. book_sub and book order subform. there are various subforms which i toggle in book order subform window by changing their properties with command button on main form. here is the code -
Code:
private sub billq_click()
Me.BOOK_SUB.SourceObject = "BILLQ"
Me.BOOK_SUB.LinkMasterFields = "[BOOK ORDER SUBFORM].FORM![ORDER ID]"
Me.BOOK_SUB.LinkChildFields = "[order ID]"
private sub stock_click()
Me.BOOK_SUB.SourceObject = "stock"
Me.BOOK_SUB.LinkMasterFields = "[BOOK ORDER SUBFORM].FORM![BOOKCD]"
Me.BOOK_SUB.LinkChildFields = "[BOOKCODE]"
Now the problem is when i change billq to stock it shows parameter entry window with caption [order id] twice. after clicking ok it does toggle the form to stock. then when i click button for billq again it shows the same thing.
the code works fine except showing parameter entry window twice with every toggle.
View 2 Replies
View Related
May 27, 2015
I have to maintain an Access form which contain a linked subform (using Master and Child fields).
Basically, in the main form (Form1), the user choose a value in a combobox and the subform (Form2) is automatically updated.
My issue is that I have a BeforeUpdate event on one field of my subform which is preventing to update the field (Cancel=true) when it does not meet the criteria. The alert msgbox should appear once if there is any error in the field but the BeforeUpdate event is always fired 3 times for unknown reason.
What I don't understand is that if I open the subform (Form2) as a main form or if i remove the child/master link fields in the subform property sheet, it is working as expected with the BeforeUpdate event being fired only once.
View 5 Replies
View Related
Jan 26, 2014
I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
View 2 Replies
View Related
Jul 26, 2006
I created a database that performs certain functions ("functional database") and I want to archive the changes I make to that database's structure in a central repository that is nothing more than another database ("repository") that I will create to track all of the revisions made to the 80 or so databases within my department.
I would like to create a link on the main form in the functional database that will, on click open the specific record in the repository database that refers to the revisions previously made to the functional database.
Any suggestions? Thanks in advance for your help.
View 1 Replies
View Related
Nov 6, 2007
I have created a query to show the fields from different tables but I am unable to add a new record. I have pin-pointed the problem to the Bottle_Speeds table. I believe it is the join that is preventing additions/edits (please look @ the attached picture) because as soon as I removed that table from the query I can add/edit.
Can anybody tell me why it is doing this and if it is possible to fix?
I would be happy if I could just edit the record, I can do a seperate form for adding.
Thanks in advance.
View 2 Replies
View Related
Jan 19, 2006
I am getting the error RunTime '2105' You can't go to the specifed record.
Just converted my test database to read from a sybase linked table rather than the development local table. It reads fine but the new record button now gives the above error. I presume I need to code myself a new record function . . .
Is this what I have to do, and if so, what's the best (and quickest!) way of going about it?
thanks
Dave
View 1 Replies
View Related
Sep 3, 2013
I have two sub forms on a main form.Subform 1 displays information which comes from a query, filtered using two combo boxes on the same form.The active record on the data sheet has two field values output to hidden text boxes on the form.
These text boxes then provide the values used for the query displayed on the 2nd sub form.When I highlight a new record in subform 1, the text box values change, but the values in sub form 2 do no.Which would be the best method to use to get this to requery?
View 10 Replies
View Related
Dec 4, 2005
Hi,
I developed a database in Access 2000 on an XP SP2 machine with linked tables to another Access 2000 database.
Everything works fine using my machine, but when a user running Windows 2000 uses the database, they cannot add a new record to the linked table (either directly using the datasheet or a form). Running a query on the table also did not let me add a new record.
I have searched the MSDN libraries, but I wasn't able to find anything.
Any help much appreciated.
View 1 Replies
View Related
Sep 6, 2006
Hi All,
I hope somebody can help me on this.
I still use Access 97.
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
Thanks in anticipation
Richard
View 1 Replies
View Related
Aug 15, 2005
Hello,
I am trying to have a command button load a form which will filter the records to those pertaining to a value selected in a combo box on a previous form and have the value for the combobox selection display in a textbox on the subform. However, I also want the subform to only display a new record, while still displaying the value selected from the combobox.
Basically, I want to select value on combobox, click button to open form and see a new record with the selected value displayed in a textbox.
So far I have used the wizard to create code for linked fields, and then pasted code for adding a new record, but the two don't seem to go together. Does order matter? Is there a better way of trying to make this work?
I have posted this a number of places and so far haven't been given any useful information. Can anyone help?
View 1 Replies
View Related
Feb 6, 2006
What I have is a form with a combo box that lets a user select the Letter Type. For 1 letter in particular I want it to open up a subform(s) that holds "options" that users can click on. My question is how would I get these options / multiple options to link to 1 record that correlates with the Letter Type?
View 2 Replies
View Related
Aug 5, 2014
I have several tables that are linked that I need to be able to add to. This is an example of the structure:
Patient Info
ID
FK_Ward - ID of Ward table
FK_Room - ID of Room table
FK_Cond - ID of Condition table
Ward
ID
WardID
Room
ID
RoomNumber
Condition
ID
Description
Unfortunately, its not the exact one as I can't post that here! What I am trying to do is to add a new patient record using information from the other tables as guides - e.g. the user selects the name of the Ward but the ID in the Ward table is entered, the User selects the number of the room but again the ID in the Room table is entered, and for the Condition same thing. So I would like to have dropdowns (or ComboBoxes) for each of these and based on the user's selections add a new record to PatientInfo using the ID values as links.
View 4 Replies
View Related
Jun 19, 2013
From what I have read, I understand you can't use the seek command on a recordset from a linked table from another database. Is that true? If so, what is the alternative to find a specific record in the table using an indexed field?
View 4 Replies
View Related
