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:
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"
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.
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.
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
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
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK WHERE qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri Masonry NSW');'
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.
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.
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.
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.
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.
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!
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.
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?
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.
First Access 2013 form being created. (i may not use proper termonology but trying).
Tables: tbl_Clients (PK=clientID auto#)-holds details of each client (Lname/Fname/MName/addr/preferences etc) tbl_Encounters (PK=encounterID auto#)-could have many encounters for each client. tbl_followup (PK=followupID auto#)-some encounters need followup, some don't, some encounters have many followups.
Have a form linking to tbl_Client, with subform to tbl_encounters (going to create another form for followups-haven't got that far yet). I would like to try to avoid duplicate clients in the tbl_Client, so when the form is opened they could look for client if not there add new. However for the existing client if say the address changed i want them to be able to do that. (i could get rid of dups in a query/report based on matching certain fields etc, however i'd like to avoid it in the first place).
I have a search form with a subform (it's a query). When you search for an entry, the results show up in my subform.
Is it possible to select that record from my subform and have it open up in my main form for making changes to that entry? Either with a control button, macro or coding?
Screen shots: The first is my search form, the second is my data entry form.
I have a form for entering property tax. The form is based on a table called investments. The form contains a combo box to select the appropriate property for data entry to the table. The two fields in the combo box are Property PIN and Property ID. The property ID is a combination of tax year+state+county+PIN. The same property can come up in more than one tax year so there are duplicate PINs but the Property ID is unique and is the key field. My problem is the combo box shows the duplicate PINs with their respective unique Property IDs as separate records but will only select the record for the earliest tax year regardless of which records I select on the combo box.
I am trying to make calculations at a single record level. I have many products and I want to select only 6 of them, which I just found out that I can do it by their SupplierID. then I have a query that finds the amount of pcs that I have for each product. The aim is to determine the final weight of this 6 products. So to start I need to find the weight of each individual product by multiplying the number of pieces (from the query) by the weight of a single piece. Obviously each product weights differently, and at the end I need to add all the weights of the individual products to make the total weight of the 6 products.
don't know how to approach the problem of selecting a single record. And also I don't know what would be best to do it in a query, a form or a report.
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?
Hi. First of all, this is my first post, so tell me if I'm being stupid.
I have a simple form, 'BuildingID', which contains a list box and two buttons. The list box lists a field from the records in the table 'BuildingID'. The two buttons are 'Add Building' and 'Delete Building'. The 'Add Building' button works fine, but I cant figure out how to make the other one work.
I want to be able to single click one of the records in the list box, then hit the 'Delete Building' button to delete the entire record. So I'm guessing I have to somehow make the record that I click on in the list box current when I click on it, then have a standard delete record button.
