I have a query with a list of Customer SSNs and Claim Rep, I want to randomly pick 5 Customer SSNs for each of the Claim Rep.
I can't figure out on how to do that, I was able to create an SQL code to just randomly pick the customer SSN.
I don't know anything about Visual Basic codes, but if that is the only solution, please go ahead post it.
Thank you very much in advanced.
I have one table (500 Club) with two fields (ID) (Name) i would like to randomly pick a record from the table and display the results in two seperate text boxes on a form one for (ID) and one for (Name). I intend to use this for a monthly draw at my workplace.
View 6 Replies View RelatedI'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.
Code:
Private Sub lblEdit_Click()
DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog
End Sub
Is there any known issues and even better fixes for records where they partially vanish into thin air, well the meaningful contents disappear and I'm left with nulls. Been an ongoing issue for quite some time, rebuilds haven't fixed the problem, although the same table, different records every time too. There are in excess of 50,000 records in this table and it's just the one here and there, on a frequency basis, say every month or so. :confused:
View 2 Replies View RelatedI have a query which picks picks up all records from a table. One field however seems to randomly not pick up data from certain records even though the data is in the table.
This has been puzzling me for some time now, does anyone have any ideas / suggestions?
I have a scenario that I tried on an Excel forum but didn't have much luck. Conditions for my assignment:
1) Long description short, I have to randomly select 2 records for each client each month.
2) The catch is that these records have to be different states until the process is done for all 50 states, so the process isn't done for 4 years and 2 months (50 months).
I attached a sample database.
My subforms are randomly adding blank records and one subform I would like to stay blank repopulates with data, though not necessarily the most recently added record. Both of these seem to happen when I navigate to other main records in the database and then return to this page.Using Access 2013, I have a large form with 10 pages. On one of the pages, I have two subforms. This is set up to gather many-to-one data. The top subform is my data entry form with three fields (two combo and a text) and a command button. The bottom subform is a datasheet displaying the three fields.
The two combo boxes are cascading, and they work great. The text box is there to collect additional info for each selection.
The command button works to
1) save the record,
2) requery both subforms in order to display the new data on the datasheet,
3) clear the combo and text boxes, and 4) set focus back to the initial combo box. It all works!
But then when I leave that main record, the horror begins: blank records (from the "many" table) show up on the datasheet and the data entry fields do not stay blank. I suspect my problem is in the command button. I added this code to the OnClick for the command button:
Code:
Private Sub addMinistryItems_Click()
'save record
If Me.Dirty Then Me.Dirty = False
'requery both subforms
Forms![BCD MAIN 2013]!Child572.Form.Requery
Forms![BCD MAIN 2013]!Ministries1.Form.Requery
[code]....
guys, here is my problem.
I created a tool to manage changes. As part of the process a support can edit a change to change the status and the requester can validate that the change is done properly. Both processes work in the same way, the diference is that the can modify different fields in the form. But here is the procedure.
Edit a task (support role): From a task management menu press the button to open a form with a list of changes to be done. Double clicking in a specific change number a new form with the change detail is opened. When the support finishs press the save button and insert the changes in a history table (to track the changes) and update the change table to update the record. Until this point everything is great.
Validate a task: (requester role): from the task management menu press a button to open a form with a list of changes to be validated. Double clicking in a specific change number a new form with the change detail is opened. When the requester finishs press the save button and save the changes in a history table (to track the changes) and the change table to update the record.
The problem is that the validate task form doesnt update the change table and doesnt insert all the fields in the history table, only some of them.
Here is the error msg:
" ILI index tool can't update all the records in the update query.
ILI index tool did not update 0 fields due to a type conversion failure, 0 records due to the key violations,1 records due to the lock violations, and 0 records due to the violation rule violations.
Do you want to continue running this type of action query anyway?
to ignore the errors and run the query, click yes.
for an explanation of the causes of the violations, click help"
Here are the sql to update and insert once the save button is pressed:
Public Sub grabarstatus()
'DoCmd.SetWarnings False
MsgBox "entered"
DoCmd.RunSQL "insert into [history] (change_id,before,after,user,modify,action) values (" & Me.Task_Num.Value & ",'" & vstatus & "','" & Me.status.Value & "','" & Forms!Login!username1 & "','" & Now() & "','Validate status');"
DoCmd.RunSQL "update [newchange] set status='" & Me.status.Value & "' where change_id=" & Me.Task_Num.Value & ";"
DoCmd.SetWarnings True
End Sub
thx very much
I'm trying to get a form to display a pick list so I can choose a staff name from the whole database and the form will go to that record. In the after update of my combobox I have:
Private Sub STAFF_ID_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "LNAME='" & STAFF_ID.Column(1) & "'"
If rst.NoMatch Then
MsgBox "The selected record can not be displayed because it is filtered out. " _
& "To display this record, you must first turn off record filtering.", _
vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
and this works OK except when I try to make it pick on 2 columns I can't get it to work. E.g. if I select Joe Smith it just goes to the first Smith. I thought if I used AND on the findfirst line and put in the first name it would work. What am I doing wrong?
I also need to make it so if I type a letter the list will go to that part of the list but I'm not sure how to do this.
I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".
My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.
Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.
Thanks in advance.
Regards Peter
I have a database that I previously ran successfully on a Windows XP system. I recently received a new PC with Windows 7 Enterprise (32 bit) that is having issues with running queries from the same database. I am still running the same version of Access (2003) on the new laptop but the queries seem to stall out and Access becomes non-responsive. Can switching from one operating system to another cause this issue? I am really just starting to use Access, so I am a novice at troubleshooting and thought that I would ask this question before digging deeper.
View 6 Replies View RelatedHi all
I have a form which is used for entering holiday requests.
It has fields such as name, type of holiday, date from and date to but it also has a list box value. This list box contains the number of days between the date from and date to and exludes weekends and bankholidays using data from a table. The data from the form is inserted into a table using an sql string when the submit button is pressed. The problem I have is that the sql does not pick up the number of days from the list box unless I click on the list box before I click on submit. It is almost as if the value is in the list box but it is simply not recognised by the sql unless I select the list box using the mouse. I have tried to select the list box using code when the submit button is pressed but to no avail. Does anyone have any ideas how I can over come this or perhaps how to physically select the list box so it is highlighted when the submit button is pressed??
Thanks all
Gareth
I have a table that consist of equipments. In it is a equipment number field. I need a way to randomly pull up these equipment numbers-kind of like a lottery. Please help.
View 2 Replies View RelatedI have a master table with several pick list columns. One field in particuliar has 3 options(fed from a separate table):
a
b
c
But, the user will have a need to select more than just 'a' for example. they will need to select 'a' and 'b', or 'b' and 'c'....etc
Can this be done in a table within Access?
Thank You,
RRA23
I can see which records are missing from the table I want to import to but this query picks up no records:
SELECT [Field1] FROM Table_Linked WHERE [Field1] NOT IN (SELECT [Field1] FROM TableA);
Yet, when I run them separately, all fields are displayed.
Am I doing something wrong with the NOT IN part of the sttement that access doesn't like? If you run this in SQL server it works fine.
I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??
Code:
CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)
when i generate a report i intermittently get a page that has the detail section highlighted. The report generates an invoice for each customer selected, a new page for each invoice with a repeating header on each page. I have used the vba to some of the formatting on the page. Usually there are between 20-40 pages generated with each report, or there is an option to print just one invoice. I get the highlight on 1 page on some set of invoices, and other everything works fine.
I can not for the life of me figure out what variables are causing the detail section to be highlighted. Does not repeat on a specific customer, place in the report, or any specific that i can tell. What settings/variables/triggers/events would cause the detail section to become highlighted blue.
Right now i can generate the report, see a blue page(usually after its printed), and the regenerate it and its not there or on a different page. The invoices uploaded are for the same person generated right after each other..
In my database, my "switchboard" consists of two listboxes: "Available Forms" and "Available Reports". The Available Forms listbox lists all the forms that can be accessed, and Available Forms listbox lists all the reports that can be accessed. I did this so that I wouldn't have to create buttons for each new form or report. They all are automatically listed in the listbox for the user to double-click on to open.
Since all my form and report names are not user-friendly (ie: fmComplicatedAndUglyName, rpComplicatedAndUglyReport) I want a way for the db admin to easily assign captions for each form that the average database user would find intuitive and easy to understand (ie: "Car Maintenance" instead of fmCarMaintenance). To do this, I built a table called tbDBObjectsCaptions consisting of these fields:
dbObject_ID (PK - Long Integer)
Caption (Text)
Next, I have an unbound form (fmDBObjectsCaptions) consisting of two subforms:
1) sbfDBObjectsCaptions_Forms, which lists all Forms with captions
2) sbfDBObjectsCaptions_Reports, which lists all Reports with captions.
Each subform's record source is tbDBObjectsCaptions with an Inner Join between the table and the MSysObjects table so that I can show only forms (Type field in MSysObjects = -32768) or only reports (Type field in MSysObjects = -32764). So, the record source looks like:
Code:
SELECT tbDBObjectsCaptions.dbObject_ID, tbDBObjectsCaptions.Caption
FROM tbDBObjectsCaptions INNER JOIN MSysObjects ON tbDBObjectsCaptions.dbObject_ID = MSysObjects.Id
WHERE (((MSysObjects.Type)=-32768));
(Except the Report's subform record source Where statement would have "-32764" instead of "-32768".)Each subform also consists of a "Caption" textbox and a combobox that lists all the forms or reports in MSysObjects. The Row Source for those comboboxes are:
Code:
SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "*sbf*") AND ((MSysObjects.Type)=-32768));
(Except the Report's combobox row source Where statement would have "-32764" instead of "-32768".)My first day playing with fmDBObjectsCaptions went fine. Both subforms' comboboxes list either Reports or Forms and would easily let me choose a form or report. The subforms would record the same "Id" from the MSysObjects table into the tbDBObjectsCaptions table and each Caption I typed in was recorded into the tbDBObjectsCaptions table for each "Id" I chose. It worked just fine.
However, the next day, I noticed that the captions I assigned for forms/reports were now assigned to different forms and reports! In fact, the fmDBObjectsCaptions no longer shows any captioned reports as their Types have somehow changed from -32764 to -32768 and are therefore now displayed in the Forms subform.
Either the dbObject_ID is somehow mysteriously changing for each record in the tbDBObjectsCaptions table or the Id is somehow mysteriously changing for each record in the MSysObjects table. I don't know how or why but that's what's happening. So now, in the fmDBObjectsCaptions form, my Forms subform is displaying the wrong captions for the forms, and is also displaying records that were originally Reports (items that have captions but blank comboboxes were originally entered in the Reports subform).
I attached a copy of the database. Any different solution that allows for easy Admin'ing of displayed form/report names?
I have disabled the Ribbon using XML code in the USysRibbon table. Everything has worked fine, except that now when I click on two different tabs that exist on my form, the Form Tools Ribbon pops up with options to go to Layout, Design View, etc., and other options, too.
I have two subforms on this tab, but neither one has a Ribbon Name set (I didn't even know how to do that when I made them) where to look to see why this is happening. The Ribbon does not show at all on the other tabs like it is supposed to. It only shows when I click on my 3rd and 4th tabs, and then it disappears again when I click on other tabs.
We will have around 100 people attending a program that will be broken into 7 (A-G) evenly. I would like to be able to have my database do this but I am unsure of where to start on this.
View 5 Replies View RelatedI've created a database with multiple subforms within the form; however some of the subforms randomly appear and disappear for no apparent reason.
View 2 Replies View RelatedI noticed that when I made a few changes to one of my queries, the results in my data sheet view were in some sort of Asian looking language. See below. Why is it doing this? I've only been using English with this program and have not changed any of my settings so I'm very confused. Also the other fields are in English. This one field is the only one I'm having issues with.
椋
椌
植
椐
脞
踖
輙
輥
[code]...
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
I'm incrementing a counter every time I click on a combobox by using the on click event and this code:
Code:
POcount = POcount + 1
I have 5 fields on my form: GWS1, GWS2, GWS3, GWS4 and GWS5.I want to store the value of the combobox when clicked into the correct GWS field. For example: If I click the combobox 3 times it would store the value of the combobox after the 1st click in GWS1, after the 2nd click in GWS2 after the 3rd click in GWS3.I tried
Code:
me."GWS & POcount" = combobox1
but that's invalid syntax.
i have a list box that fills based on the following code.....
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.
DoCmd.OpenForm "ProductDetailsEditor", , , "Products='" & Me.List91 & "' AND Client_Account_Name='" & Me.Client_Account_Name & "'"
The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID)...it just opens all of the products with that name up, starting with the first one.....