Subform Creates Two Records
Aug 23, 2005
Hi,
I have several forms with subforms attached for data entry purposes. In a nutshell - this program is for an interview - where we need the respondent to give us a listing (say as to what intices them to join a study, etc.), but for some cognitive research method we need to be able to know the order they gave us the list in then we have them rank their list, thus the order may not be in the order they gave us the list. I hope that made sense. Anyway, when a form is called and loaded the subform always adds an extra record. Below is a screen shot of what I am talking about:
http://www.geocities.com/msfstl/ScreenShot/ScreenShot.JPG
So, not only do I get an extra record, but when I am entering data and get down to the new record line, the cursor will jump up to the top of the column. What can I do to prevent the extra record and also the cursor skip? I am thinking if I resolve one issue the other will fall into place.
Thanks,
Scot
View Replies
ADVERTISEMENT
Aug 29, 2013
I have a form that uses Table A as a record source and has a subform that uses Table B as a record source. There is a one to many relationship between Table A (one) and Table B (many).The user opens the form to a set record, then uses the subform to create a new record in Table B.
This works perfectly and I can do everything I want. But sometimes the subform creates a blank entry in Table B before I enter any data. Then, once I start entering data, it creates another record in which it stores the data that I am entering.The result is two records: One that only has the date of the record's creation (I set that to be automatic upon creating a new record) and the primary key that links Table B to Table A. A second one is the "true" record, the one which stores all of the data that I am entering.
It won't happen for 10 records or something and there is no apparent pattern. In case it makes a difference, the form is set to open as the subform as the focus.
View 14 Replies
View Related
Jan 12, 2007
I have a form that has a main for, a subform, and then another subform inside the 1st subform. The form works like this:
Conact info
--->Call info and notes
---> Orders worked
Each of those being nested as they appear. The problem is, the user usually jumps strait to putting the order number in "Orders worked" before entering in any notes or info... So no record exists in it's parent form to tie to. How can I have the database create a new record in its parent form when the user types in the subform?
Thanks!
View 1 Replies
View Related
Oct 8, 2014
I have a main form with several tabs. One of the tabs is a subform with just one number control, driven by a select query. The control shows a number, and you can type in a new number, which then updates the field in the table.
BUT, the control then shows a zero, it has moved to a blank record. If you enter a number again, it will attempt to write it to a new record.
So how do I display the number, and allow a new number to be entered that will update the SAME record, and not moved to a new record?
View 1 Replies
View Related
Aug 15, 2006
I have a form that uses the following join as its control source:SELECT * FROM [Purchase Order]
LEFT JOIN [Work Orders]
ON [Purchase Order].WorkOrderID = [Work Orders].WorkOrderID;
I also enclosed a small picture of the relationship between the two tables. This is the problem I'm getting when using my purchase order form.
What I Want:
When I type in the "Work Order #" it will lookup the most recent work order with the same number and put its ID into the purchase order record.
What I'm Getting:
When I type in the "Work Order #" a brand new work order is created and the "Work Order ID" field gets the next number in the sequence.
Make note that I'm not using code for any of this. This is happening all on its own. I think the join is messing things up here. Any ideas?
View 2 Replies
View Related
Jul 4, 2013
I have a database that was created by a colleague. They have created a form for adding client records onto the database.This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.However, when we do this, it is creating two identical records on the database.
She cannot see what she has done wrong in the code, and I know next to nothing about coding,
Code:
Private Sub CmdAdd_Click()
Dim dbAddClient As DAO.Database
Dim rstAddClient As DAO.Recordset
Set dbAddClient = CurrentDb
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
[code]...
View 11 Replies
View Related
Apr 7, 2005
We use a 3D program that uses access for it's data base. For reasons I have yet to figure out when the data base is accessed tables are creted called "at_(random number)". For example at_31789. These tables sometimes contain no information or duplicate information of other tables. How can I prevent this from happening and what causes this?
Mickey :rolleyes:
View 1 Replies
View Related
Aug 29, 2007
Hello,
I am trying to do a simple division. But I am getting an #Error when Access query tries to divide 0 by 0. Here is my example:
fldOne = 0
fldTwo = 0
fldCalc: IIf(([fldOne]/[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = #Error
However:
fldCalc: IIf(([fldOne]+[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = 0
If I change the IIF to [0]+[0] (plus) then it works. So I think I am trying to find out how to divide two fields that both contain zeros? (...I think!)
Any help appreciated!
dj_T
View 13 Replies
View Related
Jul 31, 2015
When I launch my modal, I want the user to be able to 'cancel' without creating a new record. It's not doing that and creating extra 'junk' in my table. How do I prevent that?
Here's my code:
Private Sub Btn_Exception_SubModal_Click()
DoCmd.OpenForm "Frm_Exception_UpdateModal", acNormal, , acFormAdd
Forms! [Frm_Exception_UpdateModal]![clientnmbr].value = Me![clientnmbr].value
End Sub
View 6 Replies
View Related
Jan 24, 2006
Hi,
I have a form with a number of fields and buttons on it. The form is opened in "add new record" mode. I have noticed that when I move from the last field in the tab order to the first field in the tab order it saves an entry in the database.
This is causing me problems because when users move from the last field in the tab order to the field in the tab order an entry is saved to the database but it by passes all the data checks ensuring that certain fields have been filled-in etc.
I don't know why the movement between these two fields is causing an entry to be saved. I don't have code associated with either of these objects. I aslo changed the last field in the tab order to a different field and it still happens.
Any ideas or suggestions would be greatly appreciated.
View 2 Replies
View Related
Feb 1, 2005
I am upgrading a .mdb to MSSQL. The .mdb is 17MB, but the resulting MSSQL is 72MB. Tried using both the Access Upsizing Wizard and Enterprise Manager DTS. I have done this a number of times before, but never ran into this problem. Any ideas what coule be going on, and how to fix it?
View 1 Replies
View Related
Mar 16, 2007
Just been upsizing an Access DB. It seems to create views and functions for normal select statements and stored procedures are only created for insert or update statements.
As fas as a recent DBA told me, he said that views should be used sparingly as they are not very efficient. Is this the case?
I also don't see why you would create a function when you could use a stored procedure to so the same SELECT statement(s)?
View 1 Replies
View Related
Jun 3, 2006
hello!
I'm wondering how would I, in Access, make a query that would let me do a UNION and then insert that data into a table?
Is there a way that I could achieve that in one(1) query?
I've tried with INTO, but I get an error.
(i'm using access 2000-2003)
thanks!
View 1 Replies
View Related
Jul 29, 2013
I have a form that uses a text box to search for a specific record in a table.(Cotton12) This works perfectly. What I would like to implement however is if the user so wishes he can edit the data he is shown, whithout editing the actual data in cotton12 and then with this new info create a new record in cotton13.
View 1 Replies
View Related
Dec 28, 2014
I have a frontend and a backend database. As usual i keep the tables in the backend. Now I have some tables which need to be emptied and then refilled. I empty the table using the code
Code:
DoCmd.RunSQL "DELETE * FROM TransNMTbl;"
then I fill in the table using
Code:
DoCmd.RunSQL "INSERT INTO TransNMTbl SELECT * FROM TransTbl;"
The code runs fine. The problem is that the linked table is not used and the sql statement creates another TransNMTbl table in the front end and puts the data there. Bu I need the table to be in backend.
View 7 Replies
View Related
Mar 13, 2013
I have several different sub-forms that have a button that opens a new form which creates a new record. Each of the different sub-forms have a field value that needs to be passed to the new record when the other form is opened. I've tried a few solutions, but to no avail. Right now I'm using the macro functionality as follows for one of the subforms:
ACTION ARGUMENTS
--------------------------------
RunCommand SaveRecord
OpenForm frmDocumentNew, Form, , [AssociatedClientTracking]=[Forms]![sfrm_ClientTracking]![ID-ClientTracking], , Normal
OnError Next,
GoToRecord ,,New,
MsgBox =[MacroError].[Description], Yes, None,
SetProperty [AssociatedClientTracking], Enabled, Me.ID-ClientTracking
The problem I think is that I'm creating a new record so the value doesn't get passed. The new record is only created after the user begins to enter data in the new form that was opened.
View 2 Replies
View Related
Mar 14, 2013
I have a form which saves to a database correctly. It has a key field name called "code".
I can recall the data into a copy of the first form to edit by using the key field "code".
All works well, however, when I click save data from the edit form it does not alter the original data but creates another form but with the same key field called code.
View 1 Replies
View Related
Dec 28, 2014
I created a basic database in Access 2013 to keep up with jobs, and each job has a job number assigned to make each job unique (Primary Key).
At first I had it where I had to enter the new job number manually for each job. I have updated it and now it takes the last job number and adds 1 to it and then fills in the Job Number field automatically.
The problem is when I clicked the Last Record button, it will go to the last existing record, but if I click the Next Record button while on the last record, it will create a new record. It creates a new job instead of stopping at the last record. I assume it has to do with the auto numbering I have setup for the job numbers, since it didn't do this before, but I am not sure why. I ONLY want to be able to click the New Record (Blank) button to create a new job, NOT by also clicking the Next Record button.
Is there a way to force only the New Record button to add a new record? I still need the Previous and Next Record buttons to maneuver through the existing records. I just don't want to create new jobs accidentally by clicking the Next Record button when I'm at the end of the existing records. It should just stop, or say last record or something similar.
View 3 Replies
View Related
May 29, 2014
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
View 5 Replies
View Related
Apr 10, 2012
I am trying to create a form on Infopath 2007 based on a table in access 2007. I want my users to be able to fill in this form and submit it back to me in such a way that it will directly populate the table in the database. I know I can do this. However, I am unable to modify the infopath that access 2007 creates from the table. I want to change the date format. By default it takes up the date as "date and time" and I want it to be the date only.
View 4 Replies
View Related
Aug 14, 2015
I am attempting to create a customized task manager. I have created a form that has a combo box that list a series of categories. This list is pulled from a query. I also have a sub-category list that is pulled from a separate query. The relevant section of the subcategory query looks like this:
CategoriesID / subCateogiesID
1 / 1
1 / 2
1 / 3
2 / 4
2 / 5
2 / 6
3 / 7
The query has a criteria that sources the combo box on the task creator form. This filters out all other primary categories. I have a macro that auto refreshes the page after the primary category combo box is updated. The sub category combo box then displays the related sub categories.This works great as a stand alone form. However, when I attempt to use a navigation form or use the tab navigation window I get the error message "Enter Parameter Value." I know am getting this message, because the related categories query is looking for a category in the combo box on the form, which at this point in the process is missing. It also does not update once it is moved to a navigation form or tabbed window.
View 3 Replies
View Related
Nov 5, 2014
I have the following code that creates a long string that works most of the time but sometimes gets cut short. I can't understand why it does this. When it cuts the string short it cuts it short in the same place. Everything gets in the string up to/or about the following code '</Practice Name>'This string is needed to upload info to a server.
Code:
dim msg as string
msg = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbCrLf & _
"<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & vbCrLf & _
" <soap:Body>" & vbCrLf & _
" <UpdateDataForScreens xmlns=""http://mdtoolboxrx.com/"">" & vbCrLf & _
" <PatientObj>" & vbCrLf & _
[code]....
View 10 Replies
View Related
Sep 8, 2013
I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.
There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.
Here's the VBA that I'm using:
Private Sub CmdDelReturn_Click()
Dim CmdDelReturnMsg As String
CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")
[Code] ....
It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.
View 3 Replies
View Related
Sep 29, 2005
I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories.
At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget.
SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit
FROM Budget
WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1));
My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed.
I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts.
Thankyou
View 2 Replies
View Related
Nov 17, 2005
Ladies / Gents !
I am writing a database for my work environment to enter sample numbers in a predefine batch. My Form contains information to identify batch ID. Attach to this form is a subform which I would like to limit the number of records to 24.
exsample: form : as unlimited records
subform: only limited to 24 records within the master form.
Now, everytime i create a new record in the master form , I should be allowed to enter only 24 records in the associated subform.
Does anyone have an idea on how to accomplish this !! Your help would be greatly appreciated.
Thank You
View 1 Replies
View Related
Nov 8, 2007
Hi all
I have a subform which loads the items from a table the form is non editable just for viewing the items, the problem is how can i
A. limit the subform to only show the first say 10 items in the subform
B, to have a command button on the form where the the subform is on (command button not in the form) and when the user clicks it it goes to the next set of records say from 11 to 21 and displays that in the form.
i tried doing record cound but somehow record count is not there when selecting, also would the code go in the subfrom on load or on the form load where the subform is based?
any help on this would be appreciated.
thanks.
View 7 Replies
View Related
