I have looked all over for an answer, and I think there may have been a few examples on this forum that may have helped, but I only have Access 97, so can't open the example databases to fully understand!
Bascially, in the database I am trying to set up, I have Museum items, for instance a photo, and the database users would like to have a list of the names associated with that photo. (obviously for some items there will be no people associated with it, and varying numbers of people for other items!).
I need some way of having the 'associated people' data displayed on the item information form (or somewhere handy). And I need it to have the flexibility to account for differing numbers of associated people. Would using a subform solve this? And if I use a subform, where will that data then be stored? In its own table??
Can't seem to find a solution to my problem here or in a few reference books I have on my shelf here at work.
I inherited several access databases from my predecessor a while back and although I’ve fixed most of the pre-existing problems and re-programmed most of the queries and reports at this point I’m stumped on one issue. One of the access files forces me to save every query, form or report I create no matter what. If I create a query because I want to do a quick one time query I can’t just close it and say no to saving when I’m done, I have to save it and then go back and delete it if I don’t need it anymore. I can’t track down any setting or code anywhere in this one access file that would cause this behavior to be different than all of the other dozen or so databases I inherited. This becomes a big issue in the case of if I accidentally do a major change I didn’t mean to that the undo command doesn’t correct, if I go to close it saves the query if it’s been saved before and makes my error permanent and I have to go dig out the nightly backup to undo the mistake.
Any thoughts on where to find or change this setting would be great as I’ve run out of ideas on my own.
Following program i have written in access. what it does is from the query i have created picks up the sizes of doors. calculates hit1 by qty then it needs to put the value into another table alongside the correct sizes. the problem im facing is how do i update the value into another table. heres the listing.
Option Compare Database Option Explicit Dim Db As Database Dim Rs1 As Recordset Dim Rs2 As Recordset Dim lf, rh, drw1, drw2, drw3, drw4 As String Dim q1, q2, q3, q4, q5, q6, qty As Integer Dim hit1, hit2, hit3, hit4, hit5, hit6 As Integer
Private Sub Command70_Click() Dim rs1fieldcount As Integer Dim counter As Integer ''Assign a mdb ''+++++++++++++++ Set Db = CurrentDb counter = 0 Set Rs1 = Db.OpenRecordset("custdoorsize") Set Rs2 = Db.OpenRecordset("cusdoorsbase", dbOpenDynaset)
rs1fieldcount = Rs1.Fields.Count
If Rs2.RecordCount = 0 Then MsgBox "No record found in query"
Else this is where the value will come from Do Until Rs2.EOF If Rs2.Fields!left_door_size <> Empty Then lf = Rs2.Fields!left_door_size hit1 = 1 q1 = hit1 * Rs2.Fields!qty End If
If Rs2.Fields!Right_door_size <> Empty Then rh = Rs2.Fields!Right_door_size hit2 = 1 q2 = hit2 * Rs2.Fields!qty End If
If Rs2.Fields!draw1_size <> Empty Then drw1 = Rs2.Fields!draw1_size hit1 = 1 q3 = hit3 * Rs2.Fields!qty End If
If Rs2.Fields!draw2_size <> Empty Then drw2 = Rs2.Fields!draw2_size hit4 = 1 q4 = hit4 * Rs2.Fields!qty End If
If Rs2.Fields!draw3_size <> Empty Then drw3 = Rs2.Fields!draw3_size hit5 = 1 q5 = hit5 * Rs2.Fields!qty End If
If Rs2.Fields!draw4_size <> Empty Then drw4 = Rs2.Fields!draw4_size hit6 = 1 q6 = hit6 * Rs2.Fields!qty End If
Do While counter <> rs1fieldcount
This is where it finds the correct sizes where it will display the qty value
If Rs1.Fields(counter).Name = lf Then Rs1.Edit Rs1.Fields(counter).Value = q1
End If If Rs1.Fields(counter).Name = rh Then Rs1.Fields(counter).Value = q2 End If If Rs1.Fields(counter).Name = drw1 Then Rs1.Fields(counter).Value = q3 End If If Rs1.Fields(counter).Name = drw2 Then Rs1.Fields(counter).Value = q4 End If If Rs1.Fields(counter).Name = drw3 Then Rs1.Fields(counter).Value = q5 End If If Rs1.Fields(counter).Name = drw4 Then Rs1.Fields(counter).Value = q6 End If counter = counter + 1 Loop
If Rs2.EOF Then Set Rs1 = Nothing Set Rs2 = Nothing Set Db = Nothing MsgBox "Finished ....." Exit Sub Else Rs2.MoveNext
I am fairly new to access and am curious if this is possible. I have a x number of procedures, I will use 4 as an example, with those 4 procedures I have a reference table that has certain devices for those 4 procedures that need to be compared against the device charges. ie Procedure 1A 2B 3C 4D
Procedure Device Reference 1A has AAA,BBB,CCC,DDD 2B has AAA,EEE,FFF,GGG 3C has AAA,HHH,III,JJJ 4D has FFF,KKK,LLL,MMM
Device Charges has AAA,III.LLL.ZZZ
Is there maybe a case statement that I can use to have access try to solve for the best combination of the devices with the procedure to give me the most matches possible and not match say AAA with 3C and LLL with 4D giving 2 out of 4 matches, instead of the result AAA with 1A,III with 3C, and LLL with 4D, and flagging ZZZ as not a match or it matches 3 out of 4 Im not sure if this is the best place for this but any help is greatly appreciated and if more information is needed please let me know thank you for your time in advance
The recruitment database I have designed for work is okay in the main except for where I need to filter candidates depending on their skill sets. What I need to be able to do is filter candidates that possess ALL skills selected in a multiselect listbox...
Table structure runs as: Candidates (many) linked to Job Role (one) (because candidates can only have one job title (in theory anyway) Candidates linked to Skills table via junction table (candidate having many skills / one skill belonging to many candidates blah blah)...I'm sure this set up is as normalised as I can get (I aint no expert tho)...
My solution so far (suggested by another forum) was 3 listboxes on a form that runs like... ListBox 1 = Job Role (Manager, Team Leader, Clerk, etc - set to SINGLE SELECT because an employee can only have ONE job title (supposed to anyway LOL)... ListBox2= Skills (Payroll,Audit,Taxation, etc - SET TO MULTISELECT because employees can have more than one skill)... ListBox3 = Candidates (populated by making selections in ListBoxes 1 and 2)
It all works well but is VERY slow as I was told to use make table, append queries and quite a bit of VBA to make the WHERE clauses as SQL statements, etc
I've searched high and low all over the net and have found things that come close but I'm just not adept enough to work it out (I started Access late in life). I feel I need some kind of subquery that first of all finds all candidates that e.g. have ALL 3 skills selected in Listbox 2 (creating a recordset of one row per skill meaning each candidate is listed in the recordset for as many skills selected and then filtering again with a count function that only displays candidates with a count of 3 skills - this subquery would then be used to populate Listbox 3 -
Sorry if I've overcomplicated this but it seems such a simple thing to and I'm getting a lot of pressure at work having being trying to solve this for weeks...
Any help putting me in the right direction or if you know of any similar example databases that would help me learn more would be much appreciated
Ok here is what was going on. I have an option group, I would select an option, type in my search criteria into a text box, and click an event button. The program would then search my database for that criteria and grab the emails of the individuals meeting that criteria. A message box would then pop up, listed all the emails it had grabbed I would click ok (my sendobject command is set to edit the email) and at this point instead of opening the email to edit the program would crash.
After scratching my head for several days I moved on and have come back to this problem again two weeks later, and within 15min I realized what was wrong. I guess it is good to walk away and come back fresh sometimes.
What is happening is that the program does not account for entries in my database that do NOT have an email.
Example, let us say we want to search a state...lets use the state of GA for example. If I have 5 entries from GA with email addresses and one entry from GA without an email address, it will crash the program.
My only guess is that the way the program is setup is that it is grabing whatever data is in the email location. It that location is blank, its grabing blank and crashing the program.
I need a way to discard the entries it searches that don't have email addresses or something...
Please help, this is the last item for this project.
Here is the current code:
Code:'Code by M. Walts'Important information! this code requires a reference to the Microsoft DAO object libraryOption Compare DatabaseOption ExplicitPrivate Sub cmdEmail_Click()'will hold the dynamic SQL queryDim strSQL As String'will hold the WHERE clause portion of our SQL queryDim strWHERE As String'will hold all the recipients of this messageDim strRecipients As String'the recordset we will use to get the emails of the records that match our criteriaDim rst As DAO.Recordset'if there is input in the search criteria, then we will run the query and send the e-mailIf txtSearch <> "" Then'if you have more buttons, just add mosr cases (the value of the radio button'= the Case number, so Value of the State radio button is 1, etc.)Select Case opgSearch.ValueCase 1strWHERE = "WHERE State = '" & txtSearch & "'"Case 2strWHERE = "WHERE PrayerSupport = '" & txtSearch & "'"Case 3strWHERE = "WHERE Denom = '" & txtSearch & "'"Case 4strWHERE = "WHERE PACTTrainer = '" & txtSearch & "'"Case 5strWHERE = "WHERE PACTPartner = '" & txtSearch & "'"Case 6strWHERE = "WHERE City = '" & txtSearch & "'"Case 7strWHERE = "WHERE Donor = '" & txtSearch & "'"Case 8strWHERE = "WHERE MailingList = '" & txtSearch & "'"Case 9strWHERE = "WHERE Conference = '" & txtSearch & "'" Case 10strWHERE = "WHERE YouthPastor = '" & txtSearch & "'"Case 11strWHERE = "WHERE PreviousCustomer = '" & txtSearch & "'"End SelectstrSQL = "SELECT EMail FROM tblUser " & strWHERE'run the query and get the results into the recordsetSet rst = CurrentDb.OpenRecordset(strSQL)'Loop through the recordset and add all the EMailsDo While Not rst.EOFstrRecipients = strRecipients & ";" & rst!EMailrst.MoveNextLoop'remove the first ; from the strRecipientsstrRecipients = Right(strRecipients, Len(strRecipients) - 1)MsgBox strRecipientsDoCmd.SendObject , , , , , strRecipients, "Email Subject", "Email Body", Truerst.CloseSet rst = NothingEnd IfEnd Sub'stops a ' entered in the field from breaking the queryPrivate Function SQLSafe(safeMe As String) As StringSQLSafe = Replace(safeMe, "'", "''")End Function
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
I have a data entry subform that is only supposed to show an empty record ready to be populated, and a display records subform that is supposed to show all the records. The subforms are both on the same tab of a tab control on my main form.
Problem 1: The data entry subform shows all the records rather than a blank record. Something on my main form is causing it to show the records when it should not. Any ideas? The Data Entry is set to Yes.
To try to isolate the problem, I created a new form and added the subform to it where it behaves properly:confused:
I then added Me.DataEntry = True to the form open to see if that would solve my problem but it still sets the data entry to no.
If I have the properties box open when in form view of my main form, I can set the data entry to Yes and it works fine until I move to the next record of the main form when it resets to no. Teraing my hair out here.:mad:
My final attempt was to search the entire project to see if there is a "DataEntry = False" somewhere but there isn't. What is setting this property? Any ideas where I should look?
Problem 2:
After entering data in the first subform (data entry form), I want to re-query the second subform but I just can't get the syntax right. I have wrestled with the "Syntax for subs" document downloaded from http://www.mvps.org/access/forms/frm0031.htm (Microsoft MVP site) but to no avail.
My main form is called fdlgPrjDetails, the data entry is via fsubPrjCommentsUsersDataEntry and the subform I wish to requery is fsubPrjCommentsUsers.
None of the attempts below worked giving a cannot find control error.
Private Sub Form_AfterUpdate() On Error GoTo ErrHandler
im having problems getting a subform to calculate another subform's total and display it as it keeps coming up with the #NAME?.Basically my database is like this
tblCustomer - tblOrders - tblItems
and this is displayed on a form. so you can flick through clients, then flick through the orders and its broken down into the items inside each order.i have seen many answers that are only about calculating something on a subform and displaying on a form but this does not work when displaying on a 'parent' subform. the form structure looks like this
i have a calculated control textbox in the footer of sFrmCustomerOrders with sums up all the prices using =sum([Price])and in my sFrmCustomerItems, i have a field called Amount (the total amount of all the items) and the Control Source formula i was using was =[Forms]![sFrmCustomerOrders]![sFrmCustomerItems]![txtTotalPrice] but i get a #NAME? error.
I didn't know that Access is supposed to only have 2 subform deep. On my form, I have three subform deep. However, the master form is unbound, not linked to subforms, and nothing more than a container for tab pages which contains their own subforms and has no code directly associated with data. There is a code to move subform to new record when tabs changes, but that's all.
Does that mean there is really only two subforms deep from the topmost form on the tab page or the subform depth has been exceeded whether the master form has no role in data transaction?
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
i have a form with 2 subforms. when the user selects a record in subform one. the date of that record filters subform 2. in the source query of subform 2 i have this under criteria
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
I have two sub forms on a main form.Subform 1 displays information which comes from a query, filtered using two combo boxes on the same form.The active record on the data sheet has two field values output to hidden text boxes on the form.
These text boxes then provide the values used for the query displayed on the 2nd sub form.When I highlight a new record in subform 1, the text box values change, but the values in sub form 2 do no.Which would be the best method to use to get this to requery?
All I am trying to do is get a subform to requery after choosing a name from a combobox on the subform itself, thus updating the records on the subform. The main form name is "BasicTestOneF" the subform name is "ICminiBasicF" and the combobox name is "Combo4"
I have the combobox requery on After Update, but I still get the "Enter Parameter Value" popup. I've spent the last hour and a half just trying to get this simple thing to work and have gone through I don't know how many sites and forum posts, tried every combination of VBA code I've found, and I still can't get it to work.
The forms in the code are subforms on a TabControl on a main form.
I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.
At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.
Private Sub FloorNumber_AfterUpdate() If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then [Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area] End If End Sub
I have a main form that has two subforms, subform1 and subform2, both connected to the main form. When I enter data into a field in subform1 I want subform2 to requery and update to show the calculated results from the new or changed data in subform1.
I found that if I do a refresh it works when changing the data but not when entering a new line of data in subform1. I tried some code in the after update field of on subform1, but cannot seem to get anything to work.
Me.[subform2].Form.Requery
Does not work. It errors and says it cannot recognize subform2 as a field. I have tried a field name on subform2 but I still got the same error.
I have a subform within a main from which switches by entry. The subform is a datasheet. I have two dropdowns in the datasheet and I want to limit one off of the other. I keep getting a prompt. The prompt is Forms!CLTS Subform!Environment. To my understanding it looks like it can't find Environment. Does anyone know the proper format.
This is my code for the second drop down in the subform. Also special note Environment is what I am referencing to and it also is in the subform.
SELECT PackageName.PackageNameID, PackageName.PackageName, PackageName.EnvironmentID FROM PackageName WHERE (((PackageName.EnvironmentID)=[Forms]![CLTS Subform]![Environment])) ORDER BY PackageName.PackageName;
I have a main form which has a subform control in it. when the main form is opened the sub form control contains a list of events in a sub form (a summary view), for the person selected in the main form.
I want to change the subform to a different one which shows the whole event when i click in a field on the subform, based on the event i clicked in. ( i can do it by opening a new form but not with a new subform)
I can make the form change using the following code:
