I'm having a problem with a form/subform. The table I'm using includes the fields: date, personnelID, status, comments. The main form has a single field, date. The subform has the fields date, personnelID, status, comments. I have the form/subform linked using the Date field, because there are multiple instances of the same date in the table, I have to keep clicking the navigation buttons on the main form to go through all the duplicate dates to get to the next one. Is there a way I can filter the Date field on the Main form to only show one copy of each date? I had thought about creating a new table with only a date field and no duplicates, then just append all dates from the main table to this one and use it to go through the dates, however I'd rather not have to make an extra table just for this. This is basically for personnel accountability, I want to be able to select a date on the main form and display the "daily status"(present, on leave) for each person in the subform.
Even better would be if I could create a next and previous button to switch the date on the main form(I know how to do this.) but how would I get the subform to update the records to the date on the main form and keep it from going to dates that don't exist?
I need to navigate through all records for a table where a <field> = a certain "string". Ideally I would have a next record button that would know to ignore records that do not meet my criteria?
Simply put, Next available record where <field> = "string"?
I was wondering if anybody could help with this. I have searched through this forum and there doesnt seem to be an answer to this.
I have a form that i would like to validate. At present i have put the data validation on the save button which triggers a script that checks to see if certain boxes have been filled in on the form. I can only get this to work be attaching it to the onclick event on a save button, however, what i really want is to be able to ditch the save button (since access writes to the DB as it goes along) and have this script triggered whenever the user tries to navigate to either a new record/ another existing record or close the form
I tried putting this on the beforer update event, but this does not work, additionally tried doing before update event with the code inside an if me.form.dirty = true statement this did not work either, has anybody got any suggestions
Please see my code below
Thanks
Marcus
Dim sDeliveryto As String Dim sDeliveryValid As String Dim sDept As String Dim sDeptValid As String Dim sReq As String Dim sReqValid As String Dim sReqNo As String Dim sReqNoValid As String Dim sReqPoint As String Dim sReqPointValid As String Dim sOrderDetailsValid As String Dim sQuantity1 As String Dim sQuantity1Valid As String Dim sDetails1 As String Dim sDetails1Valid As String Dim sPrice1 As String Dim sPrice1Valid As String Dim sSupplier1 As String Dim sSupplier1Valid As String Dim sCostCentre1 As String Dim sCostCentre1Valid As String Dim sAccountCode1 As String Dim sAccountCode1Valid As String Dim sAuth As String Dim sAuthValid As String
If sDetails1Valid = "Invalid" Or sQuantity1Valid = "invalid" Or sReqPointValid = "Invalid" Or sReqNoValid = "Invalid" Or sReqValid = "Invalid" Or sDeptValid = "Invalid" Or sDeliveryValid = "Invalid" Or sPrice1Valid = "Invalid" Or sCostCentre1Valid = "Invalid" Or sAccountCode1Valid = "Invalid" Or sAuthValid = "Invalid" Then MsgBox "Please fill all highlighted fields on the form!!!!!" Else
DoCmd.Save MsgBox "Is all the information Correct?", vbOKCancel
I have a form that reads records from a query.. It loads the first record into the form, without issue.
Code: Sub FormLoaded() Dim r As DAO.Recordset Set r = CurrentDb.OpenRecordset("Results") ' Query we want Forms("frmmainnew").lbladdUser1bad.Visible = False
[Code] ....
Now I have have 2 buttons at the bottom of the form, one for next record and another for previous record...
Code for next record
Sub NextRecordbtn() Dim r As DAO.Recordset Set r = CurrentDb.OpenRecordset("Results") ' Query we want r.MoveNext If r.EOF = True Then
[Code] ....
I know that I have 3 records that result in the query named "Results", the next record button will bring the form from record 1, to record 2... however it will not move from the 2nd to the 3rd record...
Hi, Sorry noob here with a noob question... I built my first database for my work, and I want buttons that can navigate through the records on the form view, but I want them to be like A,B,C,D etc... When I click the button I want it to go to the first last name in the record of that letter of alphabit.... I know it can be done because at my previous employment they had that feature. Please help... I can't figure this out... :confused: Also, how do I import IMT forms into an access database???
I have a main form that displays four lines of data. I have a tab control with 3 tabs. The form displays perfectly until I navigate to one of the other tabs and back to the main tab. It appears to scroll down a couple of lines and I loose the top line of data.
I have a form and a subform for data entry.It is often the case that not all the data may have been entered and so a user might need to go to recordID 24 in 30 already entered main records.However, he needs to get to that record via a (unique) 1-1 field. How do I create the form so that as well as being able to go to the first, the last, the next, the previous record, I can go to a record of my choice.
I have a form which consists of four (4) tabbed sheets. The first sheet is the main form and the other three sheets are the linked subforms.
Pressing the tab key on the keyboard navigates from field to field in each individual form; however, I would like that when I get to the last field of each form, pressing tab moves to the next sheet or the first field of the next subform.
The problem I am getting is that when I get to the last field of the main form and/or subforms, hitting the tab button creates a new record in the specific form, which I do not want. I want to just tab to the next subform.
I have a subform that has a combobox which is bound to PersonID in the many side table. It looks up people who were previous attendees, with provision to add other people who were never attendees but exists somewhere in database or to add new attendees.
The problem with the combobox is that despite being bound to many siide table's foreign key, it somehow thinks it should assign an autonumber which causes an error 3341 whenever adding a existing record.
I've checked both the subform's recordsource, combobox's recordsource and rowsource to ensure they are bound to foreign key, not the primary key but it still acts like an autonumber. I've already checked the many side table design to ensure that foreign key is simply a number type, not autonumber...
I am having trouble establishing a subform connection (relationship) to a main form. I am using Access 2003.
I am creating a medical record database where I already have one form / subform relationship established that works. However, I created another form where I want to indicate the Physician(s) information within the main form and his specialties within a subform. I need to use the subform because one physician can have more than one specialty.
My fields within two tables are: Doctor (Table) ID Doctor First Name (Key) Doctor Last Name (Key) Doctor Address Doctor City Doctor State Specialty (Related Field)
Doctor Specialty (Table) ID (Key) Doctor First Name Doctor Last Name Specialty (Related Field)
The problem I can’t establish the relationship.:confused: Also, when I look at my relation diagram – does each table have to relate to another? Can I have two separate relationships indicated?
Okay Im trying to create a form with a text box that has a Date/Time format applied to it. I also want it to default to TBD (to be determined) but it wont allow me to have text as default because of the date format. So I have a label floating overtop of it with TBD as the caption. So i want to write code that will show the Label in the form until you click on it, then it will show the text box underneath, hiding the label, and the user can input a date. I also am trying to figure how to have TBD be visible again if the user deletes the date in the text. this is what i have so far and some of it is working...thanks to anyone who can help
With CurrentForm
!txtEditModeChange.Requery
If .Name = "SYS_HOPCoverPage" Then If !PlannedInService.Value = Null Then !PlannedLabel.Visible = True !PlannedInService.TabStop = False Else !PlannedInService.Value = Not Null !PlannedLabel.Visible = False !PlannedInService.TabStop = True End If End If End With
Ok I know that I am missing something stupid but i've been staring at it for so long I can not figure out what it is.
I have a query to show run leaders in decending order and when I run the query from the query design view it works perfectly however when I open the form that is based on that query it is not sorting the runs in decending order but sorting the players by alpha name. Every other report in my program works fine except this one...BLAAAAAAH what the heck is the problem?
I have 3 tables. Two of them are linked in a one to many relationship to a reference table. I am trying to create a form that populates data in the two forms that are connected to the reference table. Is this possible?
I am going to retype this as perhaps my original explanation was too wordy.
I need to display data from 2 tables in a continuous form. Sure I can do this with a query and a join, but the user needs to be able to add records to the form as well. When adding a new record, only one field need be entered (actually selected from a combo box) and the other 2 fields would auto-fill based on the selection.
I created lots of controls on my form, and now i cannot create anymore. I do not know what the problem is... I just get an error message that says:
"MS Office Access cannot create any more controls in your form/report. If you have deleted controls from this form/report in the past, try renaming the form/report and then add more controls to it"
I tried renaming the form already. it didnt work :confused:
I tried to create a new database (i copied all the tables, queries and forms into the new database). it didnt work.
I dont know what else to do... Please help I would dearly appreciate any help. Thank you in advance :)
If an answer to this is out there, sorry. I haven't had luck finding anything. I have a form with an unbound combo box. When you select an item from the box, the objects on the form and subform fill in based on the selection. The person using this would like to click arrows to navigate through the combo box. How do I do this?:confused:
I have a query called VOLTEST that is not bound to the form. VOLTEST sums a field in a table from a subform. When the input is finished in the subform and control goes back to the main form, I want the total from the VOLTEST to show in a field on the form that is not a tab stop. Basically, this is just for reference. I have tried to put =[VOLTEST]![SumOftest_value] in the control source of the field on the form but I keep getting "Name?" as the result instead of the sum total. I can run the query in the gotfocus of the first field in the tab order after the subform and I get the correct result in a datasheet view. So, I know the query is working correctly. I just need it to show the result in field on the form.
I have a form to edit records. When I open the form I am not able to change any of the fields until I edit a date field that has a calendar popup on the "on click" event. After that all of the fields are available for editing. Any ideas?? Thanks, Pat
I copied my mdb from one pc to another. Both pc has Access 2003 ver.
But when I try to open one of the form it gives error. The error has been attached in a Jpeg file. But when the same form runs from previous pc, it runs smoothly.
Kindly let me know why this occures and what is the solution to get rid of this.
I have a form, where the users choose a name from a pull down box and enter the date and other information. The pull down box has more than 100 names. The information such as the name, date, etc. gets stored into a table. It is fine if a certain name appears more than once on the table. I want to create some code, where if I click a button, it counts how many times each name appears in the table and outputs this information. I want the output to have the name in one column and the number of times it appears in the second column. Any ideas on how I could do this. I am very new at access and VB. I would appreciate your help. Thank you. (Basically I want to see how many times each name has filled out the form)
I created form based on a query of around 7 tables, all the fields are set to enabled and edits are allowed; however, access wont allow me to edit any of the fields in that form! I know that the query is correct because i can flick through the records and the fileds change.. please help:eek:
I'm trying to use code from a project that came with an Access book. However, I can't get it to execute, because I can't get it to compile.
Could someone PLEASE help me out?
Private Sub Search_Click() gstrWherePhys = "" If Not IsNothing(Me!LastName) Then gstrWherePhys = "[LastName] Like " & Chr$(34) & Me!LastName If Right$(Me!LastName, 1) = "*" Then gstrWherePhys = gstrWherePhys & Chr$(34) Else gstrWherePhys = gstrWherePhys & "*" & Chr$(34) End If End If If Not IsNothing(Me!FirstName) Then If IsNothing(gstrWherePhys) Then gstrWherePhys = "[FirstName] Like " & Chr$(34) & Me!FirstName Else gstrWherePhys = gstrWherePhys & " AND [FirstName] Like " & Chr$(34) & Me!FirstName End If If Right$(Me!FirstName, 1) = "*" Then gstrWherePhys = gstrWherePhys & Chr$(34) Else gstrWherePhys = gstrWherePhys & "*" & Chr$(34) End If End If
If Not IsNothing(Me!City) Then If IsNothing(gstrWherePhys) Then gstrWherePhys = "[City] Like " & Chr$(34) & Me!City Else gstrWherePhys = gstrWherePhys & " AND [City] Like " & Chr$(34) & Me!City End If If Right$(Me!City, 1) = "*" Then gstrWherePhys = gstrWherePhys & Chr$(34) Else gstrWherePhys = gstrWherePhys & "*" & Chr$(34) End If End If
If Not IsNothing(Me!Facility) Then If IsNothing(gstrWhereCust) Then gstrWherePhys = "[Facility] Like " & Chr$(34) & Me!State Else gstrWherePhys = gstrWherePhys & " AND [Facility] Like " & Chr$(34) & Me!State End If If Right$(Me!Facility, 1) = "*" Then gstrWherePhys = gstrWherePhys & Chr$(34) Else gstrWherePhys = gstrWherePhys & "*" & Chr$(34) End If End If
If Not IsNothing(Me!State) Then If IsNothing(gstrWherePhys) Then gstrWherePhys = "[State] Like " & Chr$(34) & Me!State Else gstrWherePhys = gstrWherePhys & " AND [State] Like " & Chr$(34) & Me!State End If If Right$(Me!State, 1) = "*" Then gstrWherePhys = gstrWherePhys & Chr$(34) Else gstrWherePhys = gstrWherePhys & "*" & Chr$(34) End If End If
If IsNothing(gstrWhereCust) Then MsgBox "No criteria specified." Exit Sub End If
Me.Visible = False DoCmd.Hourglass True If IsLoaded("PhysicianDB") Forms!PhysicianDB.SetFocus DoCmd.ApplyFilter , gstrWherePhys If Forms!PhysicianDB.RecordsetClone.RecordCount = 0 Then DoCmd.Hourglass False MsgBox "No Customers meet your criteria" Me.Visible = True Exit Sub End If
Else DoCmd.Hourglass True DoCmd.OpenForm FormName:="PhysicianDB", WhereCondition:=gstrWherePhys, _ WindowMode:=acHidden If Forms!PhysicianDB.RecordsetClone.RecordCount = 0 Then DoCmd.Hourglass False MsgBox "No Customers meet your criteria" DoCmd.Close acForm, "PhysicianDB" Me.Visible = True Exit Sub End If DoCmd.Hourglass False End If DoCmd.Close acForm, Me.Name
I have a continuous form reflecting events. The notes field is too large and so has to be it's own form. I made a button that is supposed to open the notes for the record. But it doesn't matter which record I'm on, it clicking the button always brings up the same note - it doesn't link the criteria and open only the correct record in the notes form. Is this because it's a continuos form? How can I fix this?
I have a form that contains a pretty large list of items and several columns and the list can be sorted by each of these columns. When a user double-clicks on a particular item, another form pops up that lets the user view/modify the item. The problem I'm having is that I still want to be able to use the navigation buttons in that second form, but right now its just saying 1 of 1 (Filtered).
The way I'm applying the sorting is by updating the Rowsource field of the list with an "Order By" statement
Hi with the help of this site, i have made big advances in learning and adapting code, in building my access FE on an SQL BE.
however, i have a problem, i cant find any information on in how to resolve.
i am using menus to navigate between forms, but i cannot synchronise the critical value between the forms.
so i am on a form with critical value, say hullID
i use a menu command to open another form, but i need the form to open with the same hullID as the critical value as the previous form which is about to close.
because i can go to the new form from any form, using the menu command, i cannot directly link the critical value,
is there a way to use the critical value from the current focus form, to the new opening form
I have made an access database for doing inventory at work. We have an inventory of approx. 700 items that need to be counted each month. I have a subform that is in datasheet view, it has Inventory IDs, product descriptions, UOMs, and a field to enter quantities. On the parent form are pictures and extra descriptions linked to each item in the subform.
The Inventory IDs all start with a 3 letter alpha code for which class of item they are PLU for plumbing, FAS for fasteners, ELD for electrical device, etc. There are about 15 different classes of these inventory items. The rest of the InvIDs are 4 digits: (PLU5453 or ELD1123)
We are using tablets to do the inventory counts, so to search for an item we can swipe through the datasheet to find the InvIDs we are looking for and then enter the quantity. After our initial inventory my supervisor asked if I could add buttons to jump to the first item in a particular class of item such as PLU0001. He showed me his address book in his phone as an example, how he can push a P button to bring him to the first name that starts with P.
I considered tabs for each class, but he wants to navigate to other items by just swiping also. I tried to make a Macro that would use Go To Record but was unable to make that work.
