Forms :: Continuous Form Not Updating Tables Correctly
Feb 27, 2015
I have a continuous form that is using a select query to populate all of the bound controls. The drop downs and text boxes are displaying the values from the correct places. But, the form is not updating in the places I expected.
The select query that is being used to populate, combines information from 3 tables.'TimeEntries' table (the one that I want records to be updated in). This holds employee ID, and numeric values for Project Name and Project Task.
'ProjectInfo' table (nothing should be updated here). This table holds other details about the project (ex. Project Name) that is used to populate a combo box.
'ProjectTasks' table (nothing should be updated here either). This table holds details about project task (ex. Task Description) that is used to populate a combo box.
The issue I am having is that when records are updated on the form, they are adding records to all 3 tables that are a part of the query, instead of just updating the time entries table.
Record Source (form) = Select Query "EmployeeTimeEntry"
View Replies
ADVERTISEMENT
Feb 8, 2006
Hello all. Hope I describe my problem correctly. :D
I have a near complete database with forms linking to tables. Tables have relationships with keys set to ref. integrity/cascade records.
Here is the problem -
My forms are meant for people to input data into these tables. However, I do not want them to change those keys.
Problem 1) If I set primary/foreign keys to "autonumber" I cannot get the relationships to recognize ref. integrity and the records will not cascade, and will not update.
Problem 2) If I set primary/foreign keys to "double integer" then if someone enters a new record into the forms, it is recorded as "zero" when it should be updating in numerical order (I have 479 records in the database, so the next new record should be 480).
Problem 3) If I change it any other way - from double integer or auto number, the tables do not update at all, or do not update correctly.
What is going on here? I thought creating forms was a fairly simple task - create the form, it is related to the table, update the form, it updates the table. I mean, duh. :confused:
Thanks anyone for your help, because Microsoft's site sux.
View 1 Replies
View Related
Jul 29, 2013
Combo box on continuous form should have the control source listed as the field on the form that will be updated. The Row Source, however, is a query that includes 2 things: the field on the form that will be updated (this part will be nonvisible in your form) and the table/query of selections you want to show up in the combo box (visible). Then ensure the Bound column is set to 1 and the Column count is set to 2 with Column Widths as 0";1"
View 1 Replies
View Related
Aug 26, 2014
I have been having issues with a subform on the attached database - for some reason it is not always updating to show corresponding records.
What I am planning, this is effectively going to be an interactive learning portal which can test users as well. There will be learning material (not yet included) and a bank of questions with corresponding multiple-choice answers, only one of which is correct. Each time the main form is loaded (Cat1MainForm) it randomly selects and orders questions, then, via a sub-form, returns three potential answers. There will then be radio buttons with which the users can answer appropriately.
If you load up Cat1MainForm and scroll through various questions you will see that sometimes the answers appear, other times not. Sometimes on one record the answers are there, you navigate away and back, and they have disappeared, and vice-versa.
View 4 Replies
View Related
Jun 12, 2013
I have a form listing tasks to which I make personnel assignments with a multi-value list field type. It takes some time to select from 15-25 employees on the list for each task, especially considering that small groups of employees will be assigned to the same selection of tasks.
What I want to do is select multiple records with the mouse, then click a command button opening a form in dialog mode with the selection list. The user then clicks to make his selections and clicks ok, which then updates the multi-select field for the selected records.
View 1 Replies
View Related
May 1, 2014
I m having a subform in datasheet view. whn i use a join query as the record source for the sub form, i am not able to add or update any record in the datasheet. Is there anyway to use a linked query as record source of the datasheet with editable property.
View 2 Replies
View Related
Jun 11, 2013
I 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 Related
May 19, 2014
My form is setup with Continuous view, with data feeding from a table. The structure is straighforward (excel style). AgentID, workHrs, holidayPaid, holidayUnpaid, sickHrs etc. The structure is not normalized, and I know the risk. This is basically only for human interpretation, not really a structure that will cause instability or patched up working of the DB.
I have the continuous form view, so it can be viewed in one single display, I have placed a Totals at the footer, which is populated by nothing but a simple = Sum(columnName). This works great in most of the computers, except one. My boss's computer. Which is where it actually needs to work. I am baffled, because I have tested it on almost every single computer in my office, everyone's work. Just his computer is playing up. I am unable to pin point the problem. The status bar keeps going "Calculating..." but even after giving it a solid 5 minute it does not seem to work !
View 5 Replies
View Related
Nov 14, 2013
I am working on a very simple DB. The two fields in question are Country and City, both as text. The idea is that the user can choose one country from a single-selection ListBox and after selecting one country the Cities multi-selection ListBox gets populated through the following SQL search. The Table Country_City contains two rows with countries and cities, nothing complicated.
Code:
Private Sub BU_AfterUpdate()
'I deselect all the selected items
'*** WHY THE IS THIS NOT WORKING ???!!! ***
For i = 0 To AppliesTo.ListCount - 1
AppliesTo.Selected(i) = False
Next i
City.RowSource = "SELECT Country_City.City " & _
"FROM Country_City " & _
"WHERE Country_City.Country = '" & ListBoxCountry.Value & "';"
After I click/select a country on the first ListBox, the second does get populated correctly. The problem I am having is if the user changes the country, then the previously selected city/cities still stay selected. I tried to reverse that by deselecting all the cities each time I change the country, but the for-next loop does not seem to be doing the job. The funny thing is that if I paste those three lines on a buttom and click it right before selecting another country, it does work.
Example:
1) I select "France" on the first ListBox.
2) I select "Paris" and "Lyon" on the second ListBox.
3) Now I select "Germany" in the first ListBox.
4) The second ListBox gets filled by all the cities in Germany, but the ones I selected before (Lyon and Paris) appear on it too, selected.
I want to reset the selection on the second ListBox in case the user selects another country on the first ListBox.
Some information on the fields:
Country: Display Control=ListBox, Row Source Type=Value List, Row Source="France", "Germany"
City: Display Control=ListBox, Row Source=Table/Query, Row Source=(empty, updates with AfterUpdate event on Country ListBox)
View 1 Replies
View Related
Dec 19, 2013
I have a form based on tblContact with a subform based on table tblCategories, and another subform based on tblCompany. I have a one-to-many relationship between tblCategories and tblCompany, with the pk in tblCategory linking with a fk in tblCompany. All of the fields in tblCategories/ sbfrm Categories are yes/no fields, not sure if that plays into my problem at all.
When a user checks one or more of the check boxes in the categories subform, closes the form and re-opens it, none of the check boxes are checked for that person. I know this has something to do with the relationships and how the categories are linked to the contact... but I can't get it straight in my mind as to how that should be set up.
View 1 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
Apr 25, 2006
Hi everyone,
I've tried several different things to get around this problem and have found out a lot about what won't work!
Basically I am building a HR database. The main table is M_CandidateDetails and the primary key is CandidateID. This is a foreign key in all of the other tables: MCandidateEducation, MCandidateEquipExp, MCandidateSector. (I've attached a screenshot of the relationships.)
Goal:
What I want to do is create a continuous form with one record for each candidate. A record will show all of the info pertaining to a candidate. The form is to be used to for searching and filtering candidates.
Attempts..
First I tried creating a query to combine all of the information. (I've attached a screenshot of the query). The only problem with that was if joe bloggs had worked in 3 different industry sectors there would be 3 records for him. (I did have problems with the query not being updatable - solved that though by using the Dynaset property of the form)
Next I tried basing the form only on the M_Candidates table and using listboxes to display the information from the many-side of the various tables. But that didn't work as you cannot use an unbound control on a continuous form.
Then I tried putting in subforms instead of listboxes but you can't use subforms on a continuous form.
I'm out of ideas now- can anyone give any tips?
View 5 Replies
View Related
Jan 4, 2013
Why is it when I click on the TblCarerdetails and try to open a record with the plus sign I am present with the following error message Enter Parameter value CarerID///The frmCarerRota seems to be working ok, the relationships seem to be ok i.e TblCarers can have many rotas i.e. a different TblRota each week (1 - M)
TblClients can have different TblRotas each week (1 - M).why the tables are not linking correctly, if I click on the TblCarer plus sign.I should see the Rota details and a plus sign. If click on the plus sign, I should see the clients details.Therefore I am assuming that my relationships are not correct, I think I need to add an association table, but where and what fields do I need to add/create.
View 4 Replies
View Related
Sep 4, 2014
I have a csv file with one row containing delimited text via a comma
Example
1, James, Smith, Manchester, email, telephone, notes etc..
Think there are 50 comma separations all together. Anyway when I go to import / link my csv into access the data that is on the first row should it create individual fields where a comma has been placed... But it has doesnt quite worked, some of the fields are created and the rest have been put on a separate row! Rather than going to a new field. Rather having 50 fields I've got 21 fields and 3 rows or delimited text...
View 10 Replies
View Related
May 29, 2015
I have a bound continuous tabular form,However, based on data content in one field of a record, I want a checkbox in the same record enabled, so the user can check it if necessary. I have created a record set using the form as shown below, and I am looping through each record. To show that my code is referring to the field with required data content, I display it as a message box and it works, yet my checkbox does not enable.
I have the code in the form_load event, however, for testing purposes I have it behind a button.If I am seeing this properly, the code behind the button enables the checkbox for ALL records once the criteria in the required field is true, and based on the last record, which has no data content, it disables the checkbox in ALL records. I also have the PK ID for each record hidden in the form. Can I utilize that to target the checkbox of each individual record??
Form Detail
-Form does not allow additions or deletions. Edits allowed
-All fields are disabled and locked
-I only want the check box to unlock if data is found in the "RequiredField" as referred to below. I have also tried if not isNull(requiredfield.value) then -enable checkbox, which yields the same results
Here is my code
Code:
Dim rstMyForm As DAO.Recordset
Set rstMyForm = Forms!MyForm.Form.Recordset
rstMyForm.MoveFirst
Do While Not rstMyForm.EOF
If Not RequiredField.Value = "" Then
[code]...
View 14 Replies
View Related
May 21, 2005
I have a table called Primary Employees and another table called Secondary Employees. I also have a form where you can add new employees, which updates the Primary Employees table. What I would like to do is, after I add a new employee have the new data update both the Primary and Secondary table at the same time. These tables have the same information within them. These two tables are currently relational to one another, but I still have to go in and manual update the Secondary Employee table after I add a new employee. I have to have two databases because of other form requirements. Any help you could provide would be greatly appreciated.
View 4 Replies
View Related
Jul 23, 2013
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function
Public Function Sort_1(SortName As String, FieldName1 As String)
DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'"
End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
View 4 Replies
View Related
Jul 30, 2014
I have a continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.
At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.
continuous form: "04 - GASTOS_BUSQUEDA"
id field on continuous form: "Gastid"
pop-up (details) form: "GASTOS_EDITAR"
id on pop-up (details) form: "editar_id"
This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):
1)
DoCmd.OpenForm "GASTOS_EDITAR", acNormal, , "[editar_id] = " & Me.Gastid
2)
DoCmd.OpenForm "GASTOS_EDITAR", , , "[editar_id]=" & Me.Gastid
3)
stLinkCriteria = "[editar_id]=" & Me![Gastid]
DoCmd.OpenForm "GASTOS_EDITAR", , , stLinkCriteria
4)
Private Sub btn_editgs_Click()
On Error GoTo btn_editgs_Click_Err
Dim strWhere As String
strWhere = "[editar_id] = " & Me.Gastid
DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere
btn_editgs_Click_Exit:
Exit Sub
btn_editgs_Click_Err:
MsgBox Error$
Resume btn_editgs_Click_Exit
End Sub
View 9 Replies
View Related
Jun 13, 2012
I'm a bit new to Access but have managed to build a very simple database.
I have a main table which I need to add 4 columns onto the end of, the data for which comes from 4 tables linked to it. Below is how they are linked.
Main table------> Table 1
Main table------> Table 2
Main table------> Table 3
Main table------> Table 4
All 4 relationships have a join type of 2 (Include ALL records from 'Main table' and only those records from 'Table1/2/3/4' where the joined fields are equal)
However, 3 of them pull back the correct data when I refresh the Main table, but the 4th one doesn't - which appears to be to be set up in the exact same way. It does bring back data, but it's the wrong data or in the wrong order.
For example, if the related field in the Main table is "Sarah" - It needs to bring back "Programme", but some Sarah's pull through Programme, some are blank and some are another option altogether. Table 4 has no duplicates or typos etc and I've tried deleting it, loading it in again and creating a new relationship but nothing seems to work.
View 5 Replies
View Related
Oct 4, 2013
I have a continuous form that displays all my customers .I want to be able to dble click a customers ID and go to there customer details form.
View 14 Replies
View Related
Aug 22, 2014
How to populate the parent form's PK to multiple records in a child subform set to a default view continuous form?
In other words, how to auto populate the primary key to multiple records displayed the subform?
View 5 Replies
View Related
Jul 19, 2013
I have a continuous form sfrmAddress which is based on my table tblAddressList. What I am trying to achieve concerns the date fields DateFrom and DateTo.
DateFrom is a bound text box txtDateFrom
I am trying to put an IIf function in the Control Source of unbound text box txtDateTo
=IIf([txtDateFrom]=[qryAddressHistory1]![LatestFromDate],Date(),[tblAddressList]![DateTo])
qryAddressHistory1 returns the LatestFromDate , so the most recent address.
The idea being if it is their most recent address, they have lived there until today, if it is not their most recent address it will display the date contained in the table (when they moved out or DateTo).
I have tried this a few different ways, double and triple checked my references and I'm usually OK with IIf so I don't understand where it's going wrong!
I should add I'm getting the #Name? error all the way down the column when I open the form.
View 3 Replies
View Related
Apr 5, 2014
I have the following code that is intended to use the three fields obtained from a record on a continuous form and display the details of the record on a single form. I have not used the findfirst method because I am having enormous problems with it. Instead I am defining a query based on the recordsource of the continuous form. This sql query gives one record as result and that record is to be used as recordsource by the single form.
Code:
Public Sub SboxSelectForm(ForName, ParName, QryName)
On Error GoTo NotFound
Debug.Print "SelectForm ForName="; ForName; " ParName="; ParName; " QryName="; QryName
Dim ResultQy As String, F As Form, P As Form
Dim db As DAO.Database
Dim Prsc As DAO.Recordset, Frsc As DAO.Recordset
[code]....
View 5 Replies
View Related
Oct 24, 2013
I have a listbox which contains a list of schools. I would like the user to be able to select 1 or all the records from that list box. From that list of selected records, my goal is to allow the user to use each school selected and use that as a record source for a continous form so I can allow the user to input a integer next to it, like school size.
Example:
Listbox (Schools)
ID, Name
1, Thomas School
2, Jefferson School
3, Washington School
4, Madison School
5, Franklink School
User select Thomas, Washington and Madison
In a Continuous Form it would list,
Thomas School, Text Box (Allow User to input School Size)
Washington School, Text Box (Allow User to input School Size)
Madison School, Text Box (Allow User to input School Size)
I then would save those records into a table.
View 6 Replies
View Related
Mar 25, 2014
I have a continuous form in my database that depending on the criteria used will display any number of records. Each record has a check box so the user can select a record for printing. Quite often the user wants to select all of the checkboxes.
Is there a way to do that in vba off of a command button.
View 7 Replies
View Related
Feb 13, 2007
Hello,
My requirement is this...
I have to use MS Access as front end with ODBC connection to Oracle 9i
DB.
The application(forms) should be able to update, delete ,insert records into oracle tables(backend).
i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to
1) search for the unique id in the main form such that the subform displays all its matches
2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW
DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE???
similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table.
the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted..
How should i do this?? i am new to MS access .VBA, any help would be highly appreciated!
Thanks so much!
View 1 Replies
View Related
