Forms :: Showing Transaction History In A Form With Foreign Key Values
Aug 8, 2015
I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?
Background:I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.
I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.
View Replies
ADVERTISEMENT
Mar 25, 2013
I am having a problem with a filter that i am trying to apply to a subform.
I have a button on the form that when clicked should filter the subform (which is in datasheet view) to the criteria i set.
This criteria will eventually run from a combo box but I wanted to just get the filter working first.
I put the following code into the onclick event of the button:
Items.Form.Filter = "Items.Form.[Master Category] = 2"
Items.Form.FilterOn = True
I chose the criteria 2 for the filter just as a test as I knew there are some records with that value in the master category field.
The problem is when ever i click the button to apply the filter it clears all the data as if it has not found any records with that value.
Is my syntax and method OK? Why its filtering everything out?
The only other thing to consider is that the field I am filtering on was set up using a lookup wizard linked to a table so the values stored are a foreign key (hence the value being 2 rather than something descriptive).
View 3 Replies
View Related
May 5, 2014
I wish to open a form based on the transaction table populate one of the foreign key fields with a selected value from a combo box from the switch board (with going to a mainform&subform). I know how to get the value of the combo box on the switchboard. I just know how to then store this value in the foreign key field. stSelectedCustomer = Forms![Switchboard].[CustomerID] gives me the value from the combo box on the switchboard. I then want to open the transaction form to open a new record and have Customer ID on this form be the stSelectedCustomer storing this value in the transaction table when the user enters values in the other fields.
View 2 Replies
View Related
Jul 16, 2013
I have a combobox with the following specs.
Row Source Type = Value List
RowSource = 1;test1;2;test2;3;test3;4;test4;5;test5;6;test6;7; test7;8;test8;9;test9;10;test10;11;test11;12;test1 2
ColumnCount = 2
ColumnWidths = "0cm;8cm"
Bound Column = 1
For some reason unknown to me, although the combobox does drop down when entered, the values are not visible until selected, then the selected item is visible in the combobox, but still isn't visible in the drop-down list.
EDIT:
I'm using Access 2010
View 9 Replies
View Related
May 9, 2013
I have a form for data entry that have multiple comboboxes. I am trying to get one combobox to base its "list" on an entry from a previous combobox. The first combobox is based on a QuantityType table and has four options.
The next combobox is based on a ProductDesign table and will have close to fifty options. I want to limit the ProductDesign combobox based on the QuantityType selection which will give the QuantityTypeID. This will refer to the QuantityTypeID linked to the ProductDesign table.I have created a query that looks like this:
SELECT Product_Design.Product
FROM Product_Design, Quantity_Type
WHERE Product_Design.QuantityTypeID = Quantity_Type.QuantityTypeID
AND Quantity_Type.QuantityType = [Forms]![Product]![Qty_Type];
This Query works and if I simply run the query I get the needed information from it.What I would like to do with this query is to populate the ProductDesign combobox with this data.I have tried putting the query in the RowSource field but I get an empty combobox without the data. The strange this is that the combobox must be getting something from the query because the length of the combo box varies based on my QuantityType selection. I.E. if I select "Single" in the QuantityType, the ProductDesign dropdown shows three empty places for data whereas if I select "Multiple" in the QuantityType I see that there is ten empty places for data.getting the combo box to actually show each option?
View 1 Replies
View Related
Jun 15, 2005
Hi Guys,
I`ve searched the forums, but not knowing the terminology, i cant find what I`m looking for.
I have a table which is use for a drop down list in my forms.
This is saved into a table, but shows up as a numerical instead of the name value.
Also In reports, I get a numerical value, instead of the text value.
Is there something I`m over looking ?
Or am I doing something totally wrong.
My relationships are correct. and the field data is right, I just cant over come the number issue.
Thanks in advance.
View 6 Replies
View Related
Jun 19, 2013
I basically have an Issue's table and a Customer Table relationship set-up.
I want to information to be viewed in the Customer table however I can only see 1 Issue where as a Customer may have 2 or 3 Issues.
I have come up with inserting the whole table into the form however I don't like this look.
View 5 Replies
View Related
Jan 30, 2014
I need some normalizing my data properly, and then showing the values in a form.
Currently, my table relationships look like this;
However, there can be multiple Genres per Band and each Genre will be applied to multiple Bands, and I know this is a Many-To-Many relationship, but I'm uncertain on how to create this properly and then show it in a subform in a form.
View 14 Replies
View Related
Jul 19, 2014
I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year
So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.
I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!
Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?
View 6 Replies
View Related
Jan 20, 2014
I have a table that deals with current data (as in member rentals of items). The normalized tables that we have been given include a rental history table. The idea is that, when the member has returned the product a history entry is made in the completed rental table.
I wish to have a button which is clicked to triggers this event. Would I program a macro to do this? In other words, would I create an event that passes those values to the relevant fields in the other table (Name, date issued, return date etc)?
View 9 Replies
View Related
May 24, 2014
I am trying to auto populate a form from a table containing CustomerID, CustomerName and PCCID_FK
Now in the form I want to auto populate the field cmbPCC when I update the cmbCustomer
using dlookup I've managed to show it manually
=DLookUp("PCCID_FK","tblCustomer","CustomerName='T est'")
It gives the ID of PCCID_FK which is what I want. how do it I modify the expression to replace the TEST part with a value in a combo box.
or is there a better way of accomplishing this.
View 1 Replies
View Related
Nov 11, 2005
Hi
I want to be able to invoke a form from a main form that allows me to add, delete or amend records in a table related to the one updated by the main form.
As many details records are being updated, I want to retain the value of a couple of foreign keys (but be able to amend them if required) based on the value of the previous record.
I have looked at the Dlookup function and wonder if this is what I should be using. If it is, I am having trouble trying to understand how you could differentiate between the first invocation of the pop up form and any subsequent table entries. The way I understand this function is that to get the value of the previous record in the table you use "ID-1", but this would almost certainly lead to an error for records being updated for a new master record, or if the table happended to be empty then there would be no ID-1 record.
Any help would be greatly appreciated.
Thanks
View 3 Replies
View Related
May 3, 2014
I have a parent record and child records. After creating the parent, there must be at least one child record.
On closing the form I could, in principle, delete the parent and child (or children), if the user changed his mind.
Or, I could presumably wrap this entire enterprise in a transaction, something along the lines referred to here: [URL] ....
View 1 Replies
View Related
Aug 27, 2014
I have a query that has a total sum , my problem is when i create form all the fields in the query is visible in the form but my total sum doesnt show up, but when i view it in query it works properly.
I don't really know what the symbol really is called i just called it total sum, the E icon in query ....
View 7 Replies
View Related
Aug 25, 2014
I have a form that requires at least a transaction code (from a combo box) and a total value field (numeric) to be entered.I have used the following code but when I click OK on the message box the form is closed.I want it to remain open so the user can enter the fields and properly save the record. As the code is in the Before Update event it does the same thing if I have Cancel = true.
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Check to make sure the TransCode has been selected and the total field doesn't = zero
'If Me.cboName.ListIndex = -1 Then
' MsgBox "A value is required for ...."
' Me.cboName.SetFocus
' Cancel = True
[code]....
View 4 Replies
View Related
Feb 7, 2014
I am building a search form in my DB. I have got this working by using a text search box on the main form which then links to a subform that shows the results. This works great, however, when the form is first opened, it shows all the records that be searched. Is there any way that I can stop this? I just want the subform to be blank until a search is ran.
View 3 Replies
View Related
Jun 24, 2014
I currently have a database which has a fairly simple form linked directly to a table, which records staff attendance information.
It generally works really well, however there is one thing I would like it to do, which is be able to hide certain records from view (rather than delete them completely).
I have a checkbox on the form (bound to a corresponding field on the table) called chkHide (the field in the table is called hide), which is not visible in form view; I have a button that is visible and when pressed by a user it will change the value of the check box to True.
So far so good. However, I would then like to have the form refresh and when it does so no longer show the record to the user (so it looks like it has been deleted, but in fact that record remains in the table).
I have tried applying a filter on load, but I don't know what expression to put in the Filter on the Property Sheet - - I have tried [Hide] = Is Null, but that isn't right. I have also created a Query where I have set the criteria for the field 'Hide' to Is Null and then put the name of the query in the Filter box - that doesn't work either, as it will let me put in new records but not let me see any of those put in before the form was refreshed.
View 4 Replies
View Related
Feb 26, 2014
I have a database with 2 tables: tbl_Client and tbl_Orders
I would like to sum the total from the sold products and show them in the form in a text field called 'Total sales" the data from the table orders appear as a sub form in the main form Client.
It would be great to have for each client appear the total sales in the form, but I'm not able to find the formula for this so the table tbl_Orders field "TotalPrice" will be sumed.
View 14 Replies
View Related
Sep 7, 2014
I am developing a new tool which needs to show the history of updates to the users, i.e.:
An observation is entered into a field on the MAIN FORM by user 1.
User 2 reviews the observation on the MAIN FORM and adds a separate comment in another field on the same form.
When the record is saved, the updates are NOT written to the Main Form Table but to the UPDATES TABLE for audit purposes.
I am also using current date/ time as a timestamp, which is protected and needs to be carried over to the UPDATES TABLE Every time a new comment is added in the MAIN FORM, I need it to be written to the UPDATES TABLE as a new record.
What I need to be able to show is:
Each comment added, as a list of readable records, in a table within the main form AND Each time the main form is refreshed/ opened the separate comment field is blank to enable each new update to be applied.
View 4 Replies
View Related
Jul 15, 2014
When I add new records to the form and close the form, the tables are updated with the new records, but the new records don't show in the form (navigation).
View 14 Replies
View Related
Jul 16, 2013
I have a password protected form that works fine. However, when you type in the password it can be seen on the screen. Is there a way I can change that so that when the password is typed in it can't be read (perhaps showing asterisks)?
This is what I'm using:
In ON CLICK event
Dim PassWord As String
PassWord = InputBox("Enter Password")
If PassWord = "MYPassword" Then
' Open Form
DoCmd.OpenForm "MYFormName"
DoCmd.GoToRecord , , acNewRec
Else
MsgBox ("You're not authorized")
End If
View 6 Replies
View Related
Jul 5, 2013
I have a tabular form which i use to input new payments for tenants.
What i'd like to be able to do, is have another form, with a combo box that i can chose my tenant from, then for a tabular form to show only the payments made for that tenant, sorted in date order.
View 1 Replies
View Related
May 3, 2015
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click()
On Error GoTo Err_Assign_Classes_Click
Dim stDocName As String
Dim msgboxstring As String
Dim stLinkCriteria As String
Me.name_found.Requery
[code]....
View 14 Replies
View Related
Jan 14, 2014
I am relatively new to Access 2007, I am having an issue with a sub form not showing all the data from a table.
Basically I have 2 tables: Headers and Line Details, they are linked via an order number. The headers will only ever have 1 record per order number but there can be multiple records on the line details table.
I have imported the data into both tables. When I open my main form, all the data from the header file is all visable and all correct for all records. However when I look at the data in the sub form, it is only showing data for some of the records.
I have checked and the data is correctly linked, and if I change the "source object" to point directly at the table in the sub form it shows the record is there. When I point it back to my sub form it is blank.
View 3 Replies
View Related
Jan 2, 2014
I have a report that is returning percentages based upon other calculated fields. Some of the percentage fields are showing #Num on the report. I've found this is due to one of the calculated values being zero.
3 Fields used:
Field: Qty
Total: Sum
Field: InitQty
Total: Sum
Field: OrderQty: Sum([Qty]-[InitQty])
Total: Expression
I need two more fields to return percentages based off of those 3 fields. This is what I started out with:
Field: InStock: [SumOfInitQty]/[SumOfQty]
Total: Expression
Field: Ordered: [OrderQty]/[SumOfQty]
Total: Expression
This works great until the query returns one that has sum of qty equal to zero. So this is what I've tried:
Field: InStock: IIf(Nz([SumOfInitQty],0),0,[SumOfInitQty]/[SumOfQty])
Total: Expression
Seems like it should work but its still showing #Num when I run the query.
View 2 Replies
View Related
Aug 2, 2014
I have my main menu form that shows the current time and uses the OnTimerEvent for this. (Set to 1000) Issue is that when I open another form it keeps showing Calculating. It does not seem to affect performance but keeps the status bar from showing the control explanations.
View 10 Replies
View Related
