Amend Top Value Via Text Or Combo Box
Jan 23, 2007
Is there a way whereby I, or another user, can amend the ‘TOP’ value of a query via a Combo or Text box entry on a form? I have searched this Forum for a solution to this very problem but have had no success. I understand how to achieve this via the queries ‘SQL’ view but cant figure out a way to do it by the way that I have already described!!
Your advice or a ‘pointer’ in the right direction would be extremely well received.
Thanks for your time.
Have a nice day...
Kind Regards
CarolW
View Replies
ADVERTISEMENT
Nov 6, 2006
Is it possible to set the "TOP" value of a query, via an unbound text box from a form ?
i.e. SELECT TOP "this number will be from a value in a text box" ...
Thanks
View 3 Replies
View Related
Feb 23, 2006
Well, the title says it all.
Is it possible to amend this hidden table? It is for my ODBC links, I would like to amend (enmass) where it is linked to and the table name. If I could amend this table, it would only take seconds.
View 1 Replies
View Related
May 30, 2006
I have a query which calculates and original amount, looks to see if there is an increase or a decrease in the amount, and if it's an increase, it adds the original amount to the amount of the increase, and gives a "revised" amount. If it's a decrease, it subracts the original amount from the amount of the decrease, and gives a "revised" amount as well. This works fine for the line item, as long as there's only one Revision to the line item.
What I need it to do, and I'm not sure if I should still be doing this at a query stage, or if it should be coded, is, if there is more than one revision to the line item, it needs to look at the new "revised" amount (from the first revision), and then add the increase or subtract the decrease from the revised amount, and give a new "revised" amount, instead of taking it from the original amount.
example of what it's doing now.
Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Original line item - $4,300 Increase - $50 New Revised amount - $4,350
What it should be doing.
Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Revised Line item - $4,800 Increase - $50 New Revised amount - $4,850
I need to have it look to see if the revision # is higher than 1, and then look at the revised amount, and do the increase or decrease at that time, and then give another "revised" amount.
Is this doable? And if so, any help would be greatly appreciated!
:confused:
Here is the Expression that I have in my query to calculate my "revised" amount. It looks at the original amount, and looks to see if it's an increase, and if it is, it adds it to the revision amount, and gives a new total for the line item, otherwise it sees that it's a decrease, and it subtracts the revision amount from the original, and gives an new total.
New Amount: IIf(tblRevisions![Increase?]=True,[revision Amount]+[Line Item Amount],[Line Item Amount]-[Revision Amount])
View 4 Replies
View Related
Mar 23, 2007
Hello,
I have a table exported from excel. I intially imported this in to access to form a list of due dates for services.
Every week I get and e mail with the updated version.
I am trying to figure out how to update the dates in the 1st table with the new weekly ones.
I can't get it to update. Also there may be the addition of new services as well.
Any tips would be grealty recieved.
Many thanks in advance.
View 2 Replies
View Related
Jan 23, 2015
I have a form that has four text boxes populated by a combo selection on a prior form; on the form in question, these four text boxes are locked, however there is a command button to allow users to amend the information in these boxes - all works fine, however the changes they make are only applied to the current record. On occasion, it would be useful if these changes were able to be sent back to the source table to amend it.
Giving the option would be easy enough with a Yes/No message box, but is there a way with some code to amend the source record of the combo?
View 5 Replies
View Related
Oct 23, 2014
I have a find duplicates query with the following SQL:
Code:
SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome
FROM tblData
WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4])))
ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;
This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match
So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.
View 3 Replies
View Related
Dec 10, 2013
I want to be able to change certain parts of a Query and Code based on the contets of a text box.
Example with Command Button Code:
Textbox contains the name Tom
This is a command button on a form that i have copied and renamed etc through VBA to Tom (contents in the textbox)
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "Qry BM to Management", acViewNormal
DoCmd.SetWarnings (True)
DoCmd.GoToRecord , , acNext
I now want to change where it says "Qry BM to Management" to "Qry Tom to Management",
Example with Command Query:
Again Textbox Contains Tom
Query has been copied but now i want to change the following SQL string from where it says Bhavins Table to Toms Table
INSERT INTO [Management Table] (Postcode)
SELECT [Bhavins Table].Postcode
FROM [Bhavins Table];
Im not sure if this is possible but i would like to know as adding a new user to my Database takes me about 10-15 minutes but I know what im doing (copy paste change specific parts of Queires and points of forms etc ) but i need to make it easy for management to add new Users....
View 2 Replies
View Related
Jun 18, 2013
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
Code:
sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "
CurrentDb.Execute sSQL, dbFailOnError
View 5 Replies
View Related
May 21, 2014
I'm looking for a way to have a text box auto fill based on the selection of a combo box on the same form. I cannot use the method i find all over the internet of using multiple columns in the combo box and basing the text box on that because the combo box already has multiple columns being used to determine its own possibilities and other combo box possibilities.
I would really just like the text box to work like this, but im still kinda inexperienced in VBA...
If combo box is "F004-001", then text box is "237"
If combo box is "F004-003", then text box is "280"
I know how to add in an "after update" thing, but i do not know how to do If/then statements.
View 1 Replies
View Related
Jun 4, 2013
I have been creating a form, based on only one table. Here I am displaying data as text boxes and subfrom from the same table. At the beginning, I was interested in controlling the display of the data according to the combo box (in this case is the PO number). Now, I would like to add another combox box which is the year (I have a column with the date, and also I have a column that shows only the years digits in my table) Also, I have a subform that display the data from the same table specifics records that I want. It means that I want to pick the year first, and then in the combox box of po number shows only option of that year, and hence the text boxes and subforms change accordingly to the two combox boxes.
View 2 Replies
View Related
Jul 22, 2014
I am trying to print on report the value of combo box which is based on the query
SELECT qryDept.DeptID, qryDept.Department
FROM qryDept;
with formula
=[Form]![FormName]![comboboxName]
But my problem is its printing ID of the combo box instead of text value which is name of the dept. say HR
View 1 Replies
View Related
Jun 25, 2006
Hi,
I have a combo box on a form with three values, when I select one of the values I want a text box to show a corresponding value.
The combo box has three values for different types of memberships; under18, over60, and standard, these have different prices. So what I want is for when one of the values is selected the text box will show the price.
Any help on how to do this? The database is below.
View 3 Replies
View Related
Jan 24, 2005
Hi,
This one is really confusing me!
I have a text box on a form that is bound to a field in a table that is populated by a combo box coded into the table whose data source is a select query.
Everything looks fine in the table, but when I display the data on the form it is displaying a different field of the source select query than the one displayed on the table.
Why is this, and how can I fix it?
View 1 Replies
View Related
Jan 27, 2005
I've a form with a combo box "OrderNumber" (to allow the user to select the correct order) and a text box "Date" which displays the date of the order selected.
The problem is that I don't know how to synchronize the boxes.
I know the procedure to do it with two combo-boxes (cmboDate=Null & cboDate.Requery in the after update property of the cbo "Order Number"), but is it possible that when I change the order selected in the combo-box, the text box shows the correct date?
Thanks for your help!
View 7 Replies
View Related
Mar 29, 2005
Hey guys
This is the problem,
I have 2 fields in a table. Cityand Country.
City Country
paris.......France
madrid....Spain
london....England
milan.....Italy
Rome.....Italy
ETC
Field "City" is connected to a combo box and field "country" to a textbox on the form.
I would like the user to select a city from the combo box. When this is done the textbox linked to the country field should automatically update to show the correct country. This should update without user intervention.
I would find it better to have the text box look up a value list rather than use a table for its source. the form is being used as data entry. The combobox linked to field "city" is also a value list.
Thanks in advance.
Nitesh
View 5 Replies
View Related
May 17, 2005
Aaaargh! It's a couple of years since I got my hands dirty in Access and I seem to have forgotten more than I thought! :(
I have a simple database which includes tables STOPS (stopID, stop name, routeID, Cost band), JOURNEY(CustID, routeID, stopID), and ROUTES (routeID, Route name, Bus co).
I have a main form with the customer number on linking to a sub-form on which the user will select the route from a combo box and then the stop from a combo box. All works fine but I want to filter the stop combo box to show only stops on the route already chosen.
I've done similar before but I have tried all sorts and failed to get anything to work. Any help appreciated.
View 3 Replies
View Related
Sep 26, 2005
I have a combo box that I use a barcode scanner with. I scan the barcode and it finds the product using the code below:
Private Sub cmbBarCode_Click()
' ADD A TRANSACTION AUTOMATICALLY
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
RS.Open "SELECT * from tblTransactions", Application.CurrentProject.Connection, adOpenStatic, adLockOptimistic
RS.AddNew
RS!OrderID = ID
RS!Barcode = cmbBarcode.Column(0)
RS!Manufacturer = cmbBarcode.Column(1)
RS!ProductName = cmbBarcode.Column(2)
RS!QuantitySold = -1
RS!Cost = cmbBarcode.Column(4)
RS.Update
RS.Close
Set RS = Nothing
lstTransactions.Requery
End Sub
What I need to happen is all the above but after it has finished or before it starts it needs to clear the contents of the box. So the barcode is entered it runs the above script and then removes the barcode from the combo box, so I am ready to scan the next barcode.
Thanks,
Danian
View 3 Replies
View Related
Sep 26, 2005
I am trying to set the result of a query as the default value for a text or combo box. I have tried setting the query as the default value in the box's property. I have also tried doing it in VB. The code looks like this
Dim SQL AS String
SQL = "SELECT Address
FROM Table1
WHERE Name = Forms!Main_frm!name_lbx.Value;"
Forms!Address_frm!address_cbx.DefaultValue = SQL
I have also tried
Dim SQL AS String
SQL = DoCmd.OpenQuery([update address_qry])
Forms!Address_frm!address_cbx.Value = SQL
Where update address_qry is the same as the above.
I keep both forms. I know how to do it by setting the queries as values in a list box, then transfering the values to the text or combo boxes. But I was hoping there was an easier way.
Thanks
Alex
View 1 Replies
View Related
Sep 26, 2005
I am trying to set the result of a query as the default value for a text or combo box. I have tried setting the query as the default value in the box's property. I have also tried doing it in VB. The code looks like this
Dim SQL AS String
SQL = "SELECT Address
FROM Table1
WHERE Name = Forms!Main_frm!name_lbx.Value;"
Forms!Address_frm!address_cbx.DefaultValue = SQL
I have also tried
Dim SQL AS String
SQL = DoCmd.OpenQuery([update address_qry])
Forms!Address_frm!address_cbx.Value = SQL
Where update address_qry is the same as the above.
I keep both forms open. I know how to do it by setting the queries as values in a list box, then transfering the values to the text or combo boxes. But I was hoping there was an easier way.
Thanks
Alex
View 2 Replies
View Related
Feb 7, 2006
Hi
I would like to know how can i reference a combo box to the value of a text box on the form it is for a purchasing system.
if i select product one i only want the the order quantity for that item to show and the same with the price field as all the reorder levels and cost information is stored in a table. i have created queries only selecting the product code and reorder quantity and the product code and the cost price.
hope someone will be able to help
regards
melanie
View 14 Replies
View Related
Oct 26, 2006
I know how to base the combo box on a query, and in the after update enter
Me.[txtboxName]= Me.[cboName].Column(1)
BUT...
I want to bound the combo box to a control source.
How can I achieve that?
Thanks~
View 3 Replies
View Related
Dec 3, 2006
I have searched and looked for this throughout the forum but can't find it.
I have a simple text box which I want to populate based on a combo box. The combo needs to display the table's field name and the text box needs to display the record. Is this possible?
Cheers,
Rene
View 3 Replies
View Related
Apr 8, 2005
Hi All,
Thanks so much for your help in advance. I’m working on a Form1 that has a combo box with Row Source coming from a query Just like:
NAME ITEM_ID
Smith 10011
Smith 10012
Scott 10022
Clark 10015
Clark 10016
Now, I want to be able to update the Item_ID value to text boxes txt_SN1 and txt_SN2 once I click on the combo box and select the name “Smith” then show 10011 in txt_SN1 and 10012 in txt_SN2. I’m not sure if this can be done with record set or just with column values from the query.
Can someone help me with this problem? Your help will be highly appreciated.
Mosquetero
View 2 Replies
View Related
Jun 23, 2005
I have one combo box and a text field. When I select the Analyst name from the combo box I want to display its corresponding Badge Id in the text field. Both the Analyst name and the Badge Id are in the same table.
Please help me out with it.
View 5 Replies
View Related
May 22, 2006
Hi there, I hope someone will take pity on me and help me as I am new to databases so here goes.
I have combo boxes in a form that the user would like to be able to press the Enter key and just go to a new line, not go to the next field. This is so they can have say 4 sentences but have each one on a new line in the combo box. Hope this makes sense.
Thanks
View 2 Replies
View Related
