i think this post may benefit others because to me it seems like a good idea,
a listbox displays 2 fields from your database
job table
id (autonumber primary key)
pay (text)
here are 5 records,
1,paid
2,paid
3,paid
4,unpaid
5,unpaid
if you make a form with a listbox showin these records..
is there a way to make the unpaid records appear as red?
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
I have a pop up reports tied to my form so the usere can press the see list report of all the equipment they just scanned in or out of the building. I am useing the Date/Time data type (General Date) What I would like to get out of the query is when the user clicks the button it will show all entries from the time right before the button is pressed AND through 5 minutes earlier. (That would be the time that the user would of started scanning the equipment.
This is my current criteria : Between Now() And Date()
This is returning all transactions for the day. I really only want all transactions for that session.
Ok I have a list box (CounselorInitials) and a listbox (AssignedToYou)
The list box has 6 columns and is using a query (AssignedToMe). The important one is the 6th one (Counselor). All working off a table called DityLog.
I want to select initials in the combo box and then it will filter the listbox and only show records that are the same.
So if I select initials MC from the combo box I want the Listbox to show all records that have the initals with MC.
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
View 1 Replies View RelatedI am trying to keep records of GST transactions.
I have a problem in figuring out how to do calculations in a query.
How to have the calculation in the attached MDB and Document for field #105_SubTotal and the field #115_PaymentEnclosed.
i have 3 tables one for student information second for Van information and third is payment.
I want to know how to get the list of students not paid based on specific month which need to mentioned may b through a form.
So I have a multi-select listbox with tick boxes and values in on my form. Is it possible to make so only the checked values are showing up and then not display the tick box?
I know I can use a combo box to display several values, but using a listbox like this would be easier on the eye.
Is it possible to use code to get a text box to show the selected items in the list box?
I have a field called "Bad Pc Part" which is a listbox. THe problem is I want it to display the contents of 3 other fields from another table.
I created a query for the Bad Pc Part field but the problem is it only displays the information in the first field of the table. It doesnt show the contents of the other columns. I need it to do this.
Please Advise3.
I have a list box that is fed by a query. I have fields that are stored in the table as numbers (Type is Double and Standard Number with 3 decimal places).
In the tables I see the numbers 10.000 which is correct, in the query is see 10.000 again correct, however the list box on the form only shows 10.00?
I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:
Column1 Column2 Column3
AddressID Address Owner
The data is being pulled from two tables:
tblAddress
AddressID (pk)
HouseNum
Street
Apt
tblOwner
OwnerID (pk)
AddressID (fk)
LastName
FirstName
Current
Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.
I've tried the following queries:
This query gives me duplicate addresses for each instance of owners:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;
This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;
Can this even be done with a query? This is driving me nuts. Help!
Scott
hi, i have list box and the source coming form Query. i want when i click on
one in list box want it will show [faults] field in textbox from query
the field [faults] is a memo can someone help me
__________________
David
I have an access form which i have turned of menus, navigation and shift key. What I am looking for is to have buttons on my form to allow users to create and delete queries from my form. I already have a listbox that shows all the queries and runs them when a user double click on them.The codes needs to run in access 2007-2013 in both 32 & 64 bit versions.
View 3 Replies View RelatedI have multiple tables (Desktops and Telephones)
A search form, to search into those tables (It searches by "User")
The search form contains a listbox that shows results (listPC)
And the following code:
Option Compare Database
Dim strUserPC As String, strUserTel As String
Dim db As DAO.Database, rsUserPC As DAO.Recordset ', rsUserTel As DAO.Recordset
Private Sub txtSearch_LostFocus()
strUserPC = ""
strUserTel = ""
[Code] .....
It works, but I have one problem. It only shows telephones or desktop, not both. It deppends on which line inside UpdateList is first.
That example searches into 2 tables:
-Telephones
-Desktops
And searches by "user".
I want to list all telephones and desktops that a user has assigned.
What should I change to show both results?
I have a field in a table where it is a lookup is a combo box. So this makes the data type of the field a number when in reality it is a text. When I have this field show in a list box it shows the number instead of the text.
View 5 Replies View RelatedI have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
Hi, folks.
I have to write a PHP web front-end to a client-server MS Access applicatin, and I think I've hit a stumbling block.
I use ADOdb as my db abstraction layer, and it has transaction functionality built in. I've tried it with Access, and while according to the output of the code the transaction has worked, I see clearly that it has not worked.
If I insert an intentionally malformed statement as part of the transaction, none of the other queries are rolled back upon the failure of said transaction.
I know this is an Access forum, not a PHP forums, but I've gotten zero response to this post on PHP forums.
Does anyone know if there's a way I can implement transactions for Access via PHP?
Cheers and thanks very much in advance.
Pablo
Hi there,
I have one table, called tblTransactions, which contains records on security market transactions. For each buy and sell order, respectively it contains one dataset with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following SQL query code gives me then the composition of my security portfolio as per any arbitrarily chosen date (here 1/10/2007):
SELECT [tblTransactions].[Ticker], sum([tblTransactions].[Quantity]) AS TotalQuantity
FROM tblTransactions
WHERE [tblTransactions].[Date]<=#1/10/2007#
GROUP BY [tblTransactions].[Ticker]
HAVING sum(tblTransactions.Quantity) >0
ORDER BY [tblTransactions].[Ticker];
For instance, for some trades like the following...
Data --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/15/07 --- AAA --- 30 --- $60
...it results in the following query results:
01/10/07:
Ticker --- TotalQuantity
AAA --- 50
BBB --- 25
01/20/07:
Ticker --- TotalQuantity
AAA --- 80
BBB --- 25
However, I would like to add a column to this query, which gives me the number of trades for each particular stock. For instance, the query result based on the sample data above should look like follows as per 01/20/07:
Ticker --- TotalQuantity --- NumberOfTrades
AAA --- 80 --- 2
BBB --- 25 --- 1
I have absolutely no idea how to achieve that. I would be very happy for any suggestions.
Best regards,
JapanFreak
I am trying to run a transaction in my databases and I’m having problems with rollback. I have an append query and a delete query that I’m trying to run Just a test I begin the transaction, run the queries and then rollback the transaction. For some reason the rollback isn’t working. Please look at my code and let me know what I’m doing wrong. Are transactions not available in DAO? Also, how do you tell if all records were updated – How do you know when to Rollback?
Sub ArchiveRecords()
Dim db As DAO.Database
Dim qdfArchive As QueryDef
Dim qdfDel As QueryDef
Set db = CurrentDb
Set qdfArchive = db.QueryDefs("qry0201ArchiveLayoffs")
Dim cnn As Connection
Set cnn = CurrentProject.Connection
Set qdfDel = db.QueryDefs("qry0202DelArchiveLayoffs")
cnn.BeginTrans
qdfArchive.Parameters(0) = 1
qdfArchive.ExecuteqdfDel.Parameters(0) = 1
qdfDel.Execute
cnn.RollbackTrans
End Sub
I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.
I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.
I do not want to use record by record VB because it is slow and I have a million+ records.
Can anybody help ?
Transactions Table (input)
Account Period Amount
1011100
1015 100
Account Summary (desired output)
Account Period Opening Movement Closing
101 10100100
10121000100
10131000100
10141000100
1015100100200
PS I need to run on Access and SQL Server
Hi,
I'm in over my head with something I've been asked to do at work as my access skills are pretty basic.
I need a sample database containing PRODUCTS and SUPPLIERS, that lets you book IN stock received from the supplier (and adds to the stock level) and lets you book OUT stock issued to staff (and reduces the stock level)
I'll have a go at reorder levels, form design, stuff like that myself, but I'm having trouble with these very basic requirements.
Does anyone know of a sample database that I can download to learn some of this from?
I need to create distinct transactions for each invoice, so that if the static data changes, such as rates of charge, it will not automatically update any history, where a historic rate has been charged.My current thinking is that I create a table and effectively archive all invoices that have been created, as they are produced.
View 4 Replies View RelatedI am building an application for a small clinic. In the interests of good database design, I don't want to be storing numbers that are derived by calculation.
A patient with insurance will have a deductable that will be entered into a field on their first (ms access) transaction form.
A calculation will put the remaining deductable into another field. No problem.
However, the NEXT visit has to look back to find previous transaction records, again perform the initial calculation and then perform another for this transaction, and again the next time and the next until the deductable reaches zero.
Additionally, since I'm not storing these values, if I pull up an old transaction form, that form has to re-perform the calculation ONLY on transactions from that date and earlier. It must not reflect visits that took place later.
So, this field must query tblTransactions, filtered by the name of the patient (a combo box called tblTransactions.Patient_Name that looks at tblContacts), the transaction date/time (stored in two date/time fields, cleverly named tblTransactions.Date and tblTransactions.Time), and the total deductable value stored in a field called tblTransactions.Deductable.
As someone who has never coded anything more complicated than DOS batch files, I'm at somewhat of a loss as to how to go about this.
Any assistance, code-wise and which Property value to put it in would be MOST appreciated.
I have a form with a listbox on it and I am trying to make a button that will delete the selected records from a table. The List box is filled from a query. Any help would be very appreciated.
Table to delete records from = tbllinkPersonel_Training
listbox = lstTraining_In
_______________________________________________
Private Sub cmdDelete_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tbllinkPersonel_Training")
If IsNull(Me.cboTraining) Then
MsgBox "No Class selected...", vbExclamation
Exit Sub
End If
For Each var In Me.lstTraining_In.ItemsSelected
With rs
.Delete
End With
Next
MsgBox "Deleted Successfully...", vbInformation
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Me.lstTraining_In.Requery
Me.lstpersonel.Requery
End Sub
____________________________________________
Thanks, Michael
I have a form with a subform which has a listbox on it which lists all the letters sent home to a student. However despite the subform being linked by admission Number the list box is showing all records and not just the ones associated with the student.
I know this is going to be really obvious but please can someone tell me what is wrong?
Here's a tough one that has been driving me crazy! (Probably easy, but I don't want to admit it!)
I have a form with a listbox called "lst_exclist". The recordsource for this listbox is the following query:
SELECT tbl_collexcludereasons.priority, tbl_collexcludereasons.excname, IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') AS enabled, tbl_collexcludereasons.priority FROM tbl_collexcludereasons ORDER BY IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') DESC , tbl_collexcludereasons.priority;
Which basically gives me this with dummy data:
2 PIPELINE ENABLED
3 HELLO ENABLED
5 GOODBYE ENABLED
1 BAD DISABLED
4 GOOD DISABLED
6 LAST DISABLED
I had two command buttons, one up arrow and one down arrow. The up arrow is supposed to move the selected record on the listbox up by exchanging the next lesser priority number with itself. The down arrow does the same. The DISABLED records are supposed to be ignored on the move up and move down procedures, meaning only ENABLED records are allowed to exchange priority numbers, and thus move up or down on the list. I tried the following code:
Me!lst_exclisthidden = Me!lst_exclist
DoCmd.SetWarnings False
Dim startingnumber As Integer
Dim endingnumber As Integer
Dim nametochange As String
Dim getchangerst As DAO.Recordset
If IsNull(Me!lst_exclist.Column(0)) = True Then
MsgBox "Please choose an entry on the above list to move.", vbCritical, "Error"
Exit Sub
Else
End If
If Me!lst_exclist.Column(2) = "Disabled" Then
MsgBox "There is no need to move a disabled selection, please enable the selection to change it's priority.", vbCritical, "Error"
Exit Sub
Else
End If
startingnumber = Me!lst_exclist.Column(0)
nametochange = Me!lst_exclist.Column(1)
endingnumber = startingnumber + 1
If Me!lst_exclisthidden = acLast Then
MsgBox "You cannot move the bottom selection on the list down, please choose another one.", vbCritical, "Error"
Exit Sub
Else
End If
While DCount("*", "tbl_collexcludereasons", "Priority = " & endingnumber & " and Enabled = -1") = 0
endingnumber = endingnumber + 1
Wend
DoCmd.RunSQL "Update tbl_collexcludereasons set tbl_collexcludereasons.priority = tbl_collexcludereasons.priority - 1 " & _
"Where tbl_collexcludereasons.priority <= " & endingnumber & " and tbl_collexcludereasons.priority > " & startingnumber
DoCmd.RunSQL "Update tbl_collexcludereasons set tbl_collexcludereasons.priority = " & endingnumber & " " & _
"Where tbl_collexcludereasons.excname = '" & nametochange & "'"
Me.Refresh
Me!lst_exclist = endingnumber
Me!lst_exclisthidden = Null
DoCmd.SetWarnings True
But it seems to loop when it gets the the area it is checking for numbers because the highest records is disabled, so it's ignored. How can I make this work? Please help!
Thanks
Vassago