Updating A Table Using A Form

Feb 7, 2007

I want to create a form that will update a table. I have two columns, an item number and a paid column. On the form I want to plug in the item number and then I want to hit ok and it will find that item number and put a value (for example "X") in the paid column.
I have tried running an update query when i hit the ok button but I can never get to work

View Replies


ADVERTISEMENT

Updating A Table Using A Form

May 14, 2005

How would I go about updating a table from a form? My table consists of employee contact information, ie.. Cell phone, pager number, home phone.
I have created a forum with unbound combo box called employee. As I choose the employee name all other fields are populating just as i want. After I update the form with the correct system contact information i want to be able to save the data and have it update the table. It appears the only data not updating the table is the anything in the combo boxes that are unbounded. Any suggestions?

View 3 Replies View Related

Updating Table Via Form

Jul 4, 2005

If you open the attached sample db you will see that I have a cascading combo box (It is working fine).

The data for the tables that are the source for the combo box are imported from an excel sheet.(This works fine)

Now my problem the data from excel is variable after the data is imported into access I want to add to it items from another table.

After the data is imported into both tblModel and TblParts I want to append to tblParts each item from tblFixed for every Model.

If you open tblPartsEXAMPLE you will see what I want to do (I added this manually).

I hope that my expanation is clear and someone can offer some advice.

Thank you in advance.

View 2 Replies View Related

Updating More Than 1 Table With One Form

Dec 30, 2004

alright I have a form and I need a couple of text boxes on there to add information to a different table than the other ones. I found this code somewhere on this forum can you tell me if I can use it for what I need, A more detailed explination of my project might be nessecary.

Code: sub AddRecord_Onclick docmd.setwarning false if not isnull(txtName1) then docmd.runsql "INSERT INTO table (areaname) VALUES('" & txtName1 & "');" txtName1.Value = Empty endif end sub

what is the proper syntex for the Insert INTO part, Table is obviously the table I want it inserted into, what is (area), is this the field I want it inserted into?

View 4 Replies View Related

Updating A Table From A Calendar Form

Jun 17, 2005

I've creating an Access project, which is at an early stage. I'm using Access 2003 with a default file format set to Access 2000.

I've been trying to update a date field within a table using this piece of code from a Calendar form. The Calendar form has a Command Button called cmd_Accept. Its sub routine is shown below. Both variables, dt (date) and fvr_id (field visit report ID) are global.

The code works - too well! Instead of updating the only the target record, it will either update the first record plus the target record or all records with the same date and I cannot understand why this should happen.

[Field Visit Report ID] is unique, being the primary key for the table, tbl_field_visit. It is AutoNumber generated. I've double-checked the table and all entries in [Field Visit Report ID] are unique. All my MsgBOX displays show what I would expect to see.

As you can see, I've also tried doing the same task using SQL - with exactly the same result.

I've been going round and round with this for days. Should I bin this and go down another route, or can anyone tell me where I'm going wrong?

Any help much appreciated.



Private Sub cmd_Accept_Click()

Dim dbs As Database, rst As Recordset
Dim strSQL As String

dt = ocxCalendar.Value
' +++++++++++++++++++++
' Field Visit Report ID now set from another sub routine. Tested and working.
' fvr_id = Form.[Field Visit Report ID]
' +++++++++++++++++++++
' MsgBox "The Field visit report ID is " & fvr_id, vbOKOnly
' MsgBox "The date is " & dt, vbOKOnly

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_field_visit")
If rst.RecordCount <> 0 Then rst.MoveFirst

MsgBox "fvr_id = " & fvr_id & " Date = " & dt, vbOKOnly
Do While Not rst.EOF
If (rst![Field Visit Report ID] = fvr_id) Then
MsgBox "The Record ID is " & fvr_id, vbOKOnly
found = True
With rst
.Edit
!Date = dt
.Update
.Bookmark = .LastModified
End With
End If
rst.MoveNext
Loop
rst.Close

' If found Then
' vbButtons = vbOKOnly + vbExclamation
' MsgBox "Record Found! " & " Date = " & dt & " Field Visit Report ID = " & fvr_id, vbButtons
' If rst.RecordCount <> 0 Then rst.MoveFirst
' strSQL = "SELECT tbl_Field_Visit.Date FROM tbl_Field_Visit WHERE tbl_Field_Visit.[Field Visit Report ID] = " & fvr_id
' Me.RecordSource = strSQL
' MsgBox strSQL, vbOKOnly
' strSQL = "INSERT INTO tbl_Books ([Date], VALUES '" & dt & "')"
' strSQL = "UPDATE tbl_Field_Visit SET tbl_Field_Visit.Date = '" & dt & "' WHERE tbl_Field_Visit.[Field Visit Report ID] = " & fvr_id
' MsgBox "strSQL = " & strSQL, vbOKOnly
' dbs.Execute strSQL
' End If

dbs.Close

Set rst = Nothing
Set dbs = Nothing

fvr_id = 0
DoCmd.Close

End Sub

View 1 Replies View Related

Updating A Table Using A Form Lookup

Aug 7, 2005

Can anyone help?

I'm having trouble storing autofilled data from one of my forms and I was wondering if anyone has any ideas.

My data is as follows

tblProducts
ProductID
Description
Nett

tblPurchaseOrders
PurchaseOrderID
PODate
Supplier

tblPurchaseOrderDetail
PurchaseOrderID
ProductID
Description
Qty
Nett

Now I have a standard order entry form which works fine, my problem is with the subform:
frmPurchaseOrderDetail
ProductID
Description
Qty
Nett

On my form Description and Nett are auto populated referencing ProductID but this information does not transfer to the table tblPurchaseOrderDetail. The main problem is that a nett price can occasionally chanege (inflation) but I need to be able to look back to a particular order for audit purposes and be able to see what I paid.

I'm sure there is w way round this, but I've been unable to figure it out. I've gone through loads of different threads on the forum and as a result am sure it can be done, I've also looked at the examples posted by several users, but none seem to approach the lookup quite from this angle, does any one have any idea...sorry for the long post, thanks.

View 3 Replies View Related

Updating Form Entries To Another Table

Jun 11, 2006

Hi all

I'd be very thankful if someone can please help me with this problem.

I have 1 Table. This Table is imported and contains data from Excel. It also contains fields I added manually. There is about 20 imported fields (full of data) and about 100 manually added fields.

Then I made a Query of this table. I made some calculations in this Query.

I then created a Form based on the Query. I then made a Combo Box. This Combo Box selects a customer, and then updates the other fields (the ones I imported from Excel) depending on the customer I selected.

So now what I want to do is this: I want the fields (updated from the query) and newly entered data to save to another newly-created table. Can someone please help me with this? Any ideas?

Thanks a lot. All input greatly appreciated.

kruger101

View 2 Replies View Related

Question About Updating Table From A Form

Jul 26, 2006

Help - I'm at a loss! I have a form that I am using for invoicing for a Trucking firm. On this form, I have three fields that I am having problems with. These fields are From, To and Mileage from a table named Location. The user selects "From" from a combo box then goes to select "To" from another combo box. The "To" field is a filtered list of only those locations which correspond to the "From" that was chosen. Once this is done, the corresponding mileage is automatically filled in and used for rate calculations. If the "To" that they want is not in the table, they can double click the field and a form to add a record to the table pops up. So far, so good.

My problem is that now the user wants to be able to add a record such as New York to be From and Miami to be To with 123 as mileage once and be able to have Miami as From and New York as To with 123 as the mileage if he needs on future invoices. Can this be done without having to enter it again?

View 1 Replies View Related

Updating Table From Form And Subform

Oct 19, 2005

I have a form to enter records in a table of staff attending training courses, in the form is a sub-form based on a query from a different table. When a staff number is entered on the main form the staff name grade etc appears on the sub-form, my problem is that I need the table to be updated from the main form and the sub-form. Is there a way of doing this?

Thanks

View 1 Replies View Related

Updating Multiple Table From One Form

Nov 8, 2006

I have a database with nine tables, and forms for data entry, no subforms. It is created for students to track all work required to complete their degree. When student information in added for a new student I need to update other tables with their student id.

The student information table has student ID defined as autonumber. How do I update the other tables with the value generated here, and should all the tables with student ID be defined as autonumber, or some other data definition?

Thank you.

View 1 Replies View Related

Updating A Table To Change A Form

Mar 14, 2007

I need to add more entrys to my form, but when I go to the ref. table it won't let me. I states too many fileds defined.

Another table lets me add to it but when I go to the form it does not show what I added.
Hope I make sense.

Thank you in advance

View 8 Replies View Related

Updating Table Field Values From A Form

Dec 5, 2004

Greetings! This is my first post.

I have two tables: 1) tblClient, 2) tblCase.

Client records from tblClient contain a field called Client_CID (Primary Key), as the Client ID. There are also fields Client_HIGH_FILE_NO, a numeric value of the last case number assigned to the specific client and Client_PREFIX that contain a unique three letter prefix that identifies the client.

Case records from tblCase contain a field called Case_CFN (Primary Key), as the Case File Number. tblCase also contains a field called Client_CID that contains the Client ID associated with the case (obtained from a combo box lookup from tblClient).

My form is frmCase bound to tblCase.

The Case_CFN is constructed by combining the value of the selected Client_CID’s Client_PREFIX with the value of Client_HIGH_FILE_NO plus 1.

I am constructing the Case_CFN on the before update event of the combo box for selecting the client. The resulting Case_CFN may appear as follows

ABC10001

Where Client_PREFIX = “ABC” and Client_HIGH_FILE_NO = 10000

Now, I need to increment Client_HIGH_FILE_NO in tblClient by 1, meaning I need to set the value of Client_HIGH_FILE_NO for the selected Client_CID to, in this example, to 10001.

Questions:

1) Is anyone familiar with this type of number scheme generally and if so any ideas?

2) Can anyone tell me how to update the value Client_HIGH_FILE_NO for the selected Client_CID?

View 1 Replies View Related

Updating Table Field Using Query Or Form?

Dec 23, 2014

I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])

This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.

View 5 Replies View Related

Forms :: Updating Fields Via Code Not Updating Table

Dec 16, 2014

I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'

Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub

When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.

View 1 Replies View Related

Updating Relational Table From Form Using Combo-boxes

Apr 15, 2005

Hi,

I have two tables, tblCountry and tblLocation. With the following structure

tblCountry
ID
Name
Text

tblLocation
ID
Country_fk
Name
Text

As you can guess tblCountry lists all of the countries, tblLocation lists all of the locations in each country, the tblLocation.Country_fk field is linked to tblCountry.ID.

I want the user to be able to edit [tblLocation].[Text] using a form. They simply select the country and then the location using combo-boxes and then add or edit the content using a textbox.

In my form I have a combo-box that displays the country names, the RowSource is set to
SELECT [tblCountry].[ID], [tblCountry].[Name], FROM tblCountry ORDER BY [Name];

I then have a second combo-box that lists the locations for the selected country. This uses an AfterUpdate() procedure to select [tblLocation].[ID] using an SQL query based on the value of the country combo-box. I.e:

SELECT [tblLocation].[ID],[tblLocation].[Name],[tblLocation].[Text] FROM tblLocation WHERE [Country_fk] = " & Me.country_box.Value & " Order By [Name]"


I want to be able to have a textbox that then displays [tblLocation].[Text] for the selected location. Thats where the problem arises. I can't find a way that will let me display any content thats available for the selected location AND let me edit it. I've tried using UpdateAfter procedures, different bindings (tables, queries based on the value of location combo-box).

Can anyone suggest how I can display [tblLocation].[Text] based on the value of the selected country/location and be able to update the information via a textbox?

Any help would be appreciated!!!

Thanks

Jon

View 3 Replies View Related

Updating Table Field From A Form W/List Boxes

Oct 31, 2005

This is my first post, but I've been lurking for sometime. I'm grateful for all the great advice given here; despite my efforts, I can't find anything directly related to what I'm doing, though.

I have a form that is populated from a query. The query has some calculated fields and some direct selection fields from a couple of tables. One of the direcly selected fields is one that I'm trying to populate from the items in a list box.

On this form, there are two list boxes, List1 and List2. The user makes selections in List1 and clicks a command button, which runs code so that the second list box is populated with the items from List1. This was shown here:

MS Article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp)

I actually just used this code and made changes accordingly so that this feature is working perfectly. However, the items in List2 need to be updated into a table's field, and this is where I'm having the problem.

I've got List2 bound to the proper field in the query, and I can manually run that query and make changes in that field fine. What I can't figure out is why I can't get the ItemsData property of the List2 control in there. For ease of code, I've added a line that copies the List2 rowsource variable to another variable so that the values can be used elsewhere. I can't seem to get the field to receive the variable in VBA, and I can't figure out how to get the values back into the query so that the query's source table is updated.

Any clues? Or is this unclear? I'm happy to give any further information. I've been working on this for more than a week, trying different things, and I'm at wits' end.

View 3 Replies View Related

Total Newbie Question - Form Updating Not Table

Dec 1, 2004

Okay I've looked around but haven't found an answer to this, if there is one in the forum please forgive me. I've created a database to help track employee passwords for differnt system I placed and Audit Trail on the form (the example from Microsoft) however it updates only the form and not the table and I need it to do both. Please Help!

Thanks

Rand

View 14 Replies View Related

Forms :: Updating Cash Account Table Through A Form

Jul 14, 2013

How would I update my cash account account table through a form? I have just started working on access and am fairly new to it.

What I am trying to do is to update my master table with all the daily sales through a form. Would I need to write a query into my form?

View 2 Replies View Related

Forms :: Updating Calculated Dates From A Form To A Table

Mar 19, 2013

I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are

ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format

The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.

Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.

View 3 Replies View Related

Forms :: Updating Table With Calculated Field On A Form?

Apr 13, 2013

A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?

View 1 Replies View Related

Forms :: Updating Record With Form - Category Table

Feb 20, 2015

Here are my tables

Question Table
ID
Question
AnswerID
CategoryID

Answer Table
ID
Answer

Category Table
ID
Category

So my form shows :
Question, Answer, Category

And I can display any existing question and make updates to the question and answer, that works fine. BUT, what I want to do is update which category a question is in. If I change the Category in the form, it updates the 'Category' in the Category table. What I want it to do is update the CategoryID in the question table.

View 2 Replies View Related

Updating Form After Adding Fields To Its Related Table

Apr 25, 2012

I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?

View 7 Replies View Related

Updating A Table Field In A Form Using Public Function?

Nov 13, 2014

I have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.

I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with the new PI Number it was blank.can't figure out what I am doing wrong.

Code:
Public Function NewPINum() As String
Dim vNum As String
Dim strYYMM As String
Dim getnextPI As String
strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then
vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3) vNum = vNum + 1 getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")Else
vNum = "001" getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
End If
End Function

View 4 Replies View Related

Trouble Updating Parent Tables With Child Table/form

Jul 6, 2006

Sorry this is so long but I am trying to make it as detailed as possible...

The Setup

My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).

The Problem

When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".

What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.

My Solution

I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").

I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?


Thanks,
Andy

View 3 Replies View Related

Modules & VBA :: Updating 100 Records In A Table Using Single Form Entry

Feb 25, 2014

I Work for an NHS organization. A department here orders prescriptions and sends them out to Gp Surgeries. I have been tasked in designing a database to make this easier for them.

Prescriptions arrive on a Pallet. Each pallet has 100 Boxes of prescriptions on it. Each box Contains 2000 Individual prescriptions. I have a table which contains the following:

Box Number
Serial Start Number
Serial End Number

When the user enters the first box number and the serial start number, i need it to calculate the serial end number and insert another 99 rows into the table and also calculate the serial start and end numbers for these other 99 records.

View 13 Replies View Related

Modules & VBA :: Updating A Field In A Form Based On Another Table Field

Sep 13, 2013

Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.

What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved