Hi All,
I hope I can explain what I am trying to achieve..
I have a "Top 1" query (Qry_Avail_StockItem), which selects the next available record in a table.
I have a main form (Frm_MasterStock) with a command button "Add Record".
If the user clicks the "Add Record" button, I need the form to go to the record that the query has selected.
Thanks in Advance..
Hi,
I have Access 2002 on Windows XP.
The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.
When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.
However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.
How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.
Thanks.
Let's say I have a query on a form that returns results. Is it possible to double-click a record in the recordset and open a form.
I am currently doing this just fine from a listbox, but am wondering if it is possible from a query.
Hi,
I don't quite know if I am heading down a blind alley here. I want to filter a form to show a subset of the records via a toggle button. Click again and you go back to the full record set.
The form is based on a query and the filter is to be based on a copy of the query with several criteria and sorts added. This will result in a record set of active projects comprising about 20% of all records (65 out of 253). The sorting sets the record order to match the main management report and so the Planner can update the active records by navigating through the record set rather than having to search for each record using the Find button.
If what I am trying to do is not the way to achieve the desired result, I'd be grateful for any pointers elsewhere.
Regards,
Keith.
Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.
The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.
This is my code in the "on close" event:
DoCmd.OpenQuery "qryUpdateEmailMinimal_True
and here's the SQL:
UPDATE tblInventory SET tblInventory.emailSentMinimal = True;
I assume what is missing is a reference to an inventoryID number. How do I do that?
Hello all, I have a couple questions. First I am trying to create a select query that will show the results that I have two fields , one is "Date" & the other is "Current" For the date I am using the a between statement "Between [start] and [end]. Under the "Current" I have "Applied" & below that I have "Overage" The problem is when I enter my date range, it shows all records with the Overage or Applied, but if I go & remove either "Overage" or Applied, then it works fine.
Is there somthing that I am missing
Second question is when using a find command box, is there a way to have it search multiple tables, if so how would I set the find box to search both tables, if the table names were called Let say Table 1 & table 2
Thanks!!!
I have searched and search and I can't find the answer to this question when I thought it would be simple. Hopefully you can put me out of my misery!
I have a access 2007 split form with a series of search boxes and a query result window. Enter info into one or more of the search boxes, click search and you get the matching results in the query window below. That works fine but I want to work with the results of the query, how can I do this?
I was looking for the user to be able to select a record and then click a command button to open that record in a form. Or in another instance select a record in the result window, have the current from close and have the primary key passed to a another form which I use to open the split form.
I just can't see how to do it but I expect there are several ways. Happy to use VBA but my experience is with Excel VBA so I'm still getting used to the objects.
Thanks in advance!
Hi,
I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.
Got any examples of code I could have a play with
I am struggling here so need any help I can get!!
Cheers
Paul
I have a list box bound to a query. If the list box retrieves two records, I am not able to select only one of them. If I click, it gets both records selected ( highlighted ). Is there any way I can select only one record?
View 8 Replies View RelatedBased upon a specific date (varDate), I want to select the record that is active (who drives the lease car).
row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013
I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.
I have 2 tables:
Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145
[code]...
So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.
The desired out put should be:
Unit
R_Date
Sold_Rate
X
25-AUG-15
200
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
View 8 Replies View Relatedhi guys,
some background on my data:
i have a table that holds Electronic Gift Card details (those plastic store gift voucher cards). it holds all details of every transaction for every card, so the card number, activations, redemptions, dates, times, transaction values etc. are all recorded in my table.
i have attached a small extraction in a spreadsheet.
a single gift card can have multiple transactions against it throughout its lifespan. there are sometimes error transactions, and i need to write a query that finds these error transactions (they are all flagged with a "1" in the [reversal flag] field, so that bit is easy) but then also then the next transaction that occurs on that card. this subsequent transaction will not be flagged with a "1", but is a reversal of the error and will have an "802" flag in the [transaction type] field.
each transaction is date stamped hh:mm:ss, but i don't know how to write a query that will
1) find gift card number that has a reversal code of "1"
2) find next transaction made on that gift card based on transaction time
3) check that subsequent transaction type is "802"
4) select both transactions
5) repeat for entire table
i can't seem to get the desired result just using a straightforward query, and i don't know much (or any!) VBA in access.
does that make any sense at all???
cheers
bazzason
Hi There,
I have a form 'Players' which has a field called Contact. When adding a new record to the Players form, the user has to select an existing contact or add a new one (they cant just type in a name).
(There is a contacts table)
So, i want a button next to the contacts field.I want it to:
1. If there is already a contact selected on the form, the contacts form open at this contact, so can be edited.
2. If there is no contact selected, the button will take the user to the contacts form, but will display the first record in the contacts table. From here the user can select the contact they want.
This is what i have (code below). With this code, if there is a contact already entered, the contacts form loads with that contact select, which is correct. However, if no contact has been selected, the contacts form opens with a blank record; whereas i want it to open at first record!
Private Sub Command90_Click()
On Error GoTo Err_Command90_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContacts"
If Me.Contact = " " Then
DoCmd.OpenForm stDocName
Else
stLinkCriteria = "[Name]=" & "'" & Me![Contact] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub
I have a form with a sub form
what I want to be able to do is use the main form and search on postcode surname and display the records that match in the subform and then allow the user to select which record to add additional data to
ahhhhhhhhhhhhhhhhhhhhh
I have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. The form of the Access database is as following:
Country Type Date Index
....... ...... ...... ......
The UserForm contains a ListBox "ListCountry" and a ComboBox "ListType" to select country and type, and a ListBox named "ListCT" with two columns for selected country and type, and two TextBox (TextDateSta and TextDateEnd) for the user to Type in start date and end date. What I want to do is to copy the records of the selected Country, Type, and Index from the start date to end date. I suppose I should use SQL like:
strSELECT = "SELECT tblIndex.*"
strFROM = "SELECT tblIndex"
strWHERE, I do not know how to define the condition here. The clause below is what I mean, but it looks weird and it does not work.
Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1
strWHERE = "WHERE tblIndex.Type = LiqForm.ListCT.List(i,0) AND _
tblIndex.Country = LiqForm.ListCT.List(i,1) AND _
tblIndex.Date > LiqForm.TextBoxSta.Text AND tblIndex.Date < LiqForm.TextBoxEnd.Text"
Next i
strSQL = strSELECT & strFROM & strWHERE
Could anyone give me some clue?
many thanks
I want to select unique country names from a recordset and now my code is
strSELECT = "select distinct country"
The problem is that ASIA and Asia are different, but it only selects one.How can I include both of them?
I posted this in the table section however it is better suited here I think.
I have a field in a table that I imported in. This field has the first/last/ and Middle initial all in one. So name=Frank H. Urtz.
So I want to set my criteria to say something like give me everything up to the first " " as expr1. Give me everything from first " " to second " " as expr2. Give me everything thing from second " " to the end as expr3.
What is the the statement I want for this?
Cardgunner
Hello All,
In my table, I have some records that are duplicated (aside from the primary key).
How can I select only one record from each set of duplicated entries? (There may be more than 2 of each of the duplicated records, but I only need to pull one of them...doesn't matter which one.)
i.e...Like a 'Find Duplicates' query, but only returning the top 1 primary key from each duplicated set.
Thanks,
Sarge.
Hi Everyone,
I want to try to restrict the user not always changing the record, so i need a Disable combo box command. And i did try it by using "Name.Enabled = False" after user select the value from the combo box, but the program won't let me do that because of the record is on focus. So what can i do? I only have an idea of after the user select the value from cbo then jump the cursor immediately to the second column, but anybody can tell me how to do that?
Thanks alot!
Regard,
alex
Hello! I am building a report that tracks excessive call-in's for my hospital's employees. Data is entered into a form which then is stored in the table (duh!) "Call-In's." I have a query that lists all those who have more than 5 call-in's.
Here is the problem: In order to get the sum of callin's per person, I did a totals field in my original query and counted on Name. But now I needed to know the sum of each person's, and so I had to do a cross-tab query that included the name, department, the count (from the first query) and then I summed on the count field. What a mess, right? Also, I added a field for If >5, Yes, No.
I based my Excessive call-in's report on the final (above) query. Like this:
Call In's table
Name, Department, Date of Call in, Call in type
Call-in's query
Name, Department, Date of Call in, Call in type, Count of Name
Call-in's crosstab
Rows: Name (GROUP BY), Department (GROUP BY)
Columns: Count (SUM) and total of Count (SUM)
Excessive Call-In's query
Name, Total of count, iif >5, Yes, No
There are already 54 records in the table, one of which has excessive call in's (more than 5).
Is there a way to run a report that not only lists the person's name and number of call in's but also the date of each one and the call in type? Both of these are fields in the call-in's table.
When I try to combine my Call-In's query with my Excessive Call In's query I get the matrix. Example: I get the same record for however many callin's they have. Thank for your help. I'm so sorry this is complicated. I've got to be doing something wrong, or at least inefficiently. Does any of this make sense? Thanks again.
Kelly (the clueless, but I'm trying)
Okay - have hunted the web for the answers to this query on and off for months. Thought I had something but it didn't work.
Problem - basic form with subform of a 'select * from table' dataset. So far I've added one textbox. I'd like this textbox to show the recordnumber selected in the subform or the KeyID of the record selected in the subform. This is so I can populate other textboxes with details of that record for editing and then control/validate data entry.
Have tried: form timer, form load and some version of seltop (not bothered about selheight at this stage) - sort of worked but not properly - at least it gave 'an' answer but not always the correct one and you had to click in and out of the subform to generate an answer.
Private Sub MySubform_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Text4.Value = MySubform.Form.SelTop
End Sub
Private Sub Form_Load()
Me.TimerInterval = 10000
End Sub
Private Sub Form_Timer()
Text4.Value = MySubform.Form.SelTop
End Sub
I think the _MouseUp sub isn't working as its not a recognised event for a subform.
I started looking at various DisplayRowID, SelRecord functions (in a separate module) run from a command button in the form footer but that's not really the right sort of thing and the referencing to the subform is a bit iffy.
I'd be grateful if anyone could advise on, even complicated, code.
Thanks,
Lez
I have a table that stores price records with a field for the effective date but there is no field for the inactive date. How can I write a query to return the price for a date that falls between recorded effective dates?
Example: A record exists for 12/03/2005 and the next is 01/04/2005. How can I find the effective price on 18/04/2005?
Thanks
I am trying to run a sql statement in vba in which it selects all the records in a table related to a order Id number. I then want it loop through all the records and check for a check box which is selected. if its selected then "A" variable becomes "1" and i will then run a if statement saying if variable is 1 then run this code else run code b.
The problem I am having is that if there are 4 records the code checks through all of the 4 records and keeps changing the variable so
record 1 variable is 0
record 2 var is 0
" " " 1
" " " is 0
so although the records for a given order number does have a checked box the code wont recognise that.
What I can do or what code I can add my minds gone all blank on this.
How can I select the first record of each year working with a table like this:
Code:
ID, value, date
0, 30, 01/01/2000
1, 40, 03/02/2000
2, 20, 10/03/2000
3, 10, 02/05/2001
4, 20, 09/08/2001
5, 10, 01/02/2001
I'd like to get this result from my Query:
Code:
30, 01/01/2000
10, 01/02/2001