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 Replies
ADVERTISEMENT
Jan 6, 2012
I have created a 'junction' table between 2 many to many tables:
Jobs table (details all jobs)Jobparts table ('junction' table details Job ID, Part ID, quantity and unit price)Stock table (details all stock).The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.
I discovered you can automatically populate the unit price field using autolookup based on the entry of the Part ID field on the form. However having amended some pre-existing jobpart details randomly on the form when I checked back to the table they had not updated. Similarly when creating new entries while the form 'unit price automatically updated on entry of the 'Part ID' these details did not feed through into the table.
I assume it has to do with how the table was originally set up the field properties will need to be changed.
View 7 Replies
View Related
Apr 22, 2005
I have a contact dB with 2 contacts (main & alternate). I would like to be able to pull any record containing either the main or alt last name up. Right now the unbound combo box works fine pulling up the first contact name but I need to be able to pull up a record if the second contact name matches as well. I am trying to use 1 unbound combo box to look at both fields (LastName1 & LastName2). Is it possible and how should I set it up?
View 14 Replies
View Related
Jul 30, 2005
I have a form with a combobox based on a query of a lookup table. If the name entered is not found in the combobox, I want a popup form to open allowing the user to enter a record for this new name in the lookup table, then close. What is the best way to this? A command button? The Not in List Event? A msgbox? I'm not sure.
View 7 Replies
View Related
Oct 1, 2005
Hi all,
I am wandering if anyone can help me here. I have a form which is linked to a table called tbl_File. Within in this form & tbl_File there is a field called Country_Code. In the Country_Code field the user places a number that corresponds to their country they want.
Now Country_Code & the corresponding country is defined in a seperate table called tbl_CountryCodes.
What I would like to do is in my form once the user has inputed a number in the Country_Code field (it will be a combo box), a label or textbox in the SAME form will then do a search of the corresponding Country name in tbl_CountryCodes and then display it to the user.
Any ideas will be greatful :D
Karv
View 1 Replies
View Related
Jul 14, 2006
I have my main form which is connected to my main table - Query 1
I have a lookup table (actually a view from SQL Server) which holds my user name - the username used to login to the database- just one value.
In my main form I have a 'Logged By' field. I want this field to default to my login name when I process a record.
I thought it would be as easy as connecting the lookup table to my main form and then in default value typing My_login.user_name.
Apparently not. It just has the #Name? in there.
Anyone have an opinion on this ?
I can use the =environ("username") in the default value which works to good effect but different users use each others PC and I would like the field to be defaulted to thier database login and not thier windows on.
Please help ! It seems so small and its driving me ceeerazy !
View 1 Replies
View Related
Aug 23, 2006
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID
Customer Name
ProductID is a combo box.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want it to lookup that ID from tableProduct. However, how can I do this when everything is already bound to tableData?
Thanks in advance.
View 1 Replies
View Related
Aug 23, 2006
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID
Customer Name
ProductID is a combo box on the form.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want a separate textbox to lookup that ID from tableProduct and display the ProductName.
How can I accomplish this?
Thanks in advance.
View 2 Replies
View Related
Jan 11, 2014
i am working on a school database, in data base i have create two tables tblAccounts and tblTransaction and a form frmTransaction .
tblAccounts contain two fields
GLcodes
Description
and frmTransaction contain
Glcode
transaction type
debit
credit
date
narratives
in form when i enter a glcode, lookup field match the code from tblaccounts and shows the description in form against gl code.But i am facing a problem when i enter a wrong gl code my form accept it and move to the next field and when i leave blank field of glcode same problem that i am facing, i want that , when i enter a wrong glcode in a form amsgbox will apear that asking for correct glcode.
View 5 Replies
View Related
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
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
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
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 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
