I have two tables that I want to update from data in an unbound form. How do I capture the ID of the new record created in table 1 in order to write it to a field in the new record being created in table 2.
Table 1
id
f1
f2
f3 and so on
Table 2
ID
Table 1 id
F2
F3 and so on
I have a form, which has a search box. This allows you to search through the id numbers of each record. Once an ID has been selected a bound textbox displays the id number. Other unbound textboxes then display other information from the table with the relevant record.
What I want to do is then allow users to change information in textboxes and update the record.
i have an unbound form with the following code which works as it should
Code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tbl_Courses", dbOpenDynaset)
With rst
.AddNew
rst!DateCreated = DateCreated
rst!Originator = Originator
[code]....
I also need to add other text fields from the unbound form to another table but can't seem to work out how to do it.
How to update unbound textbox on main form from unbound textbox in subform afterupdate.
that is when amount paid is updated it automatically updates total paid, balance etc.
why my vba code wouldn't work.I have a scorecard form, and what I wanted to do on a form was that if a user enters a number in a bound box (to a table), it would automatically update an unbound box. So I coded it as this.
Private Sub SupplierCorrectiveActionRequests_Enter()
If [Request] = 0 Then [Text30] = 100
Elseif [Request] = 1 Then [Text30] = 75
Elseif [Request] = 2 Then [Text30] = 50
Elseif [Request] = 3 Then [Text30] = 25
Elseif [Request] >= 4 Then [Text30] = 0
End Sub
But it throws an error that there cannot be Then if there are no ifs, weird.Another problem I had is that I'm using a continuous form, and whenever I scroll down there seems to be a visual bug,is there any way to do that without changing the theme?
How do you update table with unbound checkbox? I'd like to add basic yes/no, true/false on update. Currently I'm using a workaround like this
Code:
If Me.chkInkt = True Then
status = "true"
Else
status = "false"
End If
Which is ok if there's one of these, but sometimes I have more...
I wrote a database for my daughter's dance school. There are two tables, one for personal details, the other for exam results. The data is separated this way because when I was given it all, I got it from two people in an Excel file.
When I programmed it, I had the Primary Key in the Master table auto-generated (an Integer) which I also used as the Primary Key in the ExamResults table. All the data manipulation is handled in code by Event Procedures.This is the code to update the Master Table when a new pupil's details has been entered and the 'Submit' button on the form clicked:
Code:
Private Sub btnSubmit_Click()
On Error GoTo btnSubmit_Error
Dim curDatabase As Database
Dim rstNameAddress As Recordset
[code]....
The woman who uses it told me the other day that she can't update the exam results for any pupil she has put into the database since I gave it her - records of pupils entered by me are OK.When I checked the code I realised that when a new record is created, there isn't a corresponding one in ExamResults. when is the Primary Key first generated and how do I program the form to update the new pupil's details in the ExamResults form when the Master table is first written to?
I have an unbound form in which a user enters data relating to a credit dispute, and when they hit the Save button I have a sub that is intended to insert that data into a table. This is a bit out of my comfort zone, so I found some code from another forum and adjusted it to my needs. However, I'm getting errors.
The table name is tbl_complaints. The fields from the form are:
txtAcctNumber
txtClient
txtEoscarType
txtEoscarControl
cboMethodReceipt
....
My code is below.
Private Sub cmdSave_Click()
Dim StrSql As String
On Error GoTo ErrorHandler
StrSql = "INSERT INTO tbl_complaints " _
& "(ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT,
[Code] ....
The error I get is error number 3075, and it says I'm missing an operator in the section that references cboResponseType, but I can't seem to figure out what I'm missing. My head is spinning from staring at these parentheses and apostrophes for so long.
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 RelatedHi,
I want to show some text from another table based on the values of the bound fields in each record of a continuous form. I thought it would be easy, but I can't get it to work.
I step through the records in the bound recordset and use some of the values to query another table. Then I use the value from the query to populate the unbound text field.
It works fine for each record. But every time the unbound field is updated, all the records in the continuous form are updated.
I'm stuck on this one. It's probably something simple to fix - if you know what to do!
Do you have any suggestions?
Thanks!
-Daniel
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.
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
I have form with an unbound combo box which is populated from a query
It should populate a field in a table which is related to another - however it only works when i query by id number (primary key) and not the text value i want - is it possible to query both so it returns say 1,option 1 - then when selected it will place a 1 in the table - which is related to option 1 in another table instead of just having to select '1' which will mean nothing to a user?
Using DAO recordset, table enters unbound fields data properly for desired records, but adds an extra record which is a duplicate of the first record entered....any known access quirks for adding unwanted records?
View 3 Replies View RelatedIs there any particular reason I would choose to use
DoCmd.RunSQL "insert into...
Versus
CurrentDb.Execute "insert into.... ??
I am using an unbound form on the front end of my database to populate the backend tables (multiple tables). Some forms only have a few fields, while other forms have about 15 fields with 3-4 tables updating.
I am okay using the DoCmn.RunSQL , but I keeping reading on the forum that others chose CurrentDB.Execute and it has me thinking.
My database is stored on a network drive with only on front end.
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.
I have a unbound text box in the form header and have a command button in form I am wanting to put the unbound textbox value into a bound textbox on form.
View 1 Replies View RelatedI have a form with a navigational control which loads in the unbound area a form according to the navigational btn, this form has a list of records in datasheet view, when clicking on a specific records it opens a form with the specific record selected, I would, once double clicked on the record, the form to open within the same unbound window.
To be more precise, from the attachment, when the frmMain is open and the user double clicks on the IATACode the navigation target under the Tab Airport closes the form "frmQueryAirport" and opens "frmAirport", once the user closes the window with the appropriate Exit Btn, the "frmQueryAirport" reopens.
May not be using the correct terminology here. I have a form that currently does not have a RecordSource. From this form, I want to create a table called "ICEA" with the following fields (which would be entered from the form): OperatingDay, Hour_Ending, Import, Export, and Net.Can this be done with a SELECT statement or does it have to be done through VBA?
View 4 Replies View RelatedI have a continuous form with a number of unbound fields and bound fields. However, I would like to sum an unbound fields (Text28). Text28 is an unbound calculated field based on another calculated unbound field (Text22). I would like to sum Text28 in the footer or somewhere in the form.
Here are the codes for my field:
Code:
Text28 = iff([Text22>0], [Text22], [Text20] --- This works
Text29 =sum(iff([Text22>0], [Text22], [Text20]) --- this is not working
and it did not work.
My code is not working.
1. The code below should equal the Current Month
2. It does not close the form if no data.
PHP Code:
If DLookup("[CurMonth]", "tblEmpEvaluation") = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmmm ")
DoCmd.OpenForm "frmEvalNotice"
End If
PHP Code:
If DLookup("[CurMonth]", "tblEmpEvaluation") <> Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmmm ") Then 'No Data. NotWorking?
DoCmd.Close acForm, "frmEvalNotice"
End If
I have a form that I would like to have blank fields when it is opened. I put the following in the "On Open" Event field of the form properties:
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
However, it does not work. I have an unbound combo box on the form for users to select from, in which all the records on the form will populate with whatever is selected in the unbound combo box. I thought this was the problem at first, but even if I remove that from the form, the form still opens to the first record. If I set the "Data Entry" field to "Yes" on the Data tab, that works, but then none of the fields populate when using the combo box.
I have set up a Parameter query which works, and use this on a form.
What I am having a problem with is using an unbound field on the form to be the parameter.
For example in the Empty feild/box on the form I would type SMITH, and this on a click of a button next to it would run the parameter query as a subform.
I have an unbound form with 2 tabbed Controls on it.On the first Tabbed Control my command button code works but not for the 2nd tabbed control. Although the command buttons themselves work as far as going to the appropriate record.
What I mean by this is that I use code to enable/disable the command buttons depending on what record you are on.
Example: If there is only 2 record, the other command buttons will be disabled, Do I have to refer to the Tab Control ?
I found a few regarding continuous form data but its not the sample that fits.
Attached here is my screen shot and my accdb file (MSAccess 2013)
How do they populate unbound continuous form from msaccess connecting to mysql server.
It should display 5 data but in my case it does show only one data.