I have a database that has student table (I am not a student doing an assessment!) with a one to many relationship to coursebooking and then a course table that has a one to many relationship with the coursebooking table so coursebooking seems to be acting as the link table between the many to many relationship. (I didnt design it).
I am creating forms to make the booking process faster and to perform the correct sums to produce an invoice and update a financial transactions table along the line.
I have an entry form that creates the student, and then other forms synchronise to the master form and gather the data. I am now at the point of wanting to get product details so ex course A, taxi to school and enrolment fee and add these to the booking table.
Historically the process was messy and couldnt calculate course A cost (per week) times by number of weeks.
I want to be able to select the products from a combo to build up an invoice and then once acknowledged as details correct commit to the tables coursebooking and transaction table.
I am not sure how best to achieve this ie is it best to have a bound form to the booking table and add new records to it line by line or to use an unbound form and some kind of product picker and to see the products built up perhaps using vba and storing products in an array until ready to post.
I have a form which I've spend many hours designing to look and behave as I need it. It is bound to a table (get's it's data from there). I now want to add one or more new fields to the table (which I've done ok), but I can't seem to get the Form to see those fields. I've tried creating new controls on the form and the control source list to select from doesn't include my new fields. Even if I write VB code embedded in the form to refer to the fields, the debugger trips on the field names saying no such method/object. The only way around it I've discovered is to create a new bound form which will mean re-dooing al the layout work again.
Surely this is a common situation which has an easy solution ?!!
I have a form based on a query. I'd like to add a search box to the top to look up values and then populate the rest of the form. I can't do it with the combo box wizard because the relevant (third) option doesn't show up, I assume that's because the form is based on a query not a table.
I have a form AddNewEquipment. This is bound to a table, EquipmentDetails.
EquipmentDetails has a Yes/No field, 'ParentChoice'. So when EquipmentDetails.ParentChoice = Yes, I want to open up a new text box, AddNewEquipment.ParentDescr, into which someone can put some text. This text I want to append as a new row in a different table, ParentList.ParentName. (that table also has an autonumber field)
I only want to do the save when I save the whole form.
Is this something like using an On Lost Focus event from the ParentDescr field which only invokes when the overall form save button is clicked? What would I put in the On Lost Focus event.
There is a button on the form that allows users to send email with the form as an attachment in pdf. I'd like to create a date/time stamp in another table called tblLog. Trouble is the code works uptil sending emails but it doesn't record the stamp.
I would like to populate form fields with records from a table/query so that they are bound to the recordsource and the record to be displayed can be selected from a combobox. I am hoping that in this way, any changes made are propagated to the original table.
I have created a combobox from which the primary key can be selected and added all the fields from the table appearing on this form as values.
In my Change event for the combobox I have this code:
Private Sub cboID_Change() Me.RecordSource = "SELECT b01_Participants.*FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(1) & "));" Me.Refresh End Sub
However, I receive an 3075 error when this code runs which seems to relate to my syntax or an extra ")".
It's an unusual request, I know, but it's necessary for my purpose.
I have a form, frmMain that I use to update a number of tables. On it is a number of combo boxs, comProduct, comBrand, comSize, comPrice and comQuantity. comProduct opens a new form, frmNew if the users input is not already in the table. On frmNew there are again combo boxes comProduct, comBrand and comSize. This form adds the record to the table.
So, if I input a new value - not already in the database - for product in the comProduct combo box, the form frmNew appears and I can proceed to define the product, brand and size of the new record. The form then closes and reopens frmMain and tabs across to comQuantity (skipping the fields I've already defined in frmNew).
Now if you can wrap your head around that, here is the problem: Because I've already entered the values for product, brand and size in frmNew, I want them to automatically appear in the appropriate combo boxs on the frmMain form. I've been using the following code (executed from frmNew, so Me = frmNew):
It works, but I've since discovered it creates a new record in the table so I end up with two duplicate records (one when I submit frmNew and another when the values are automatically input into frmMain.)
Now, my question: How can I display these values in frmMain without having them create the second record? I was thinking of using a SELECT query somehow, but I don't know where to begin. I also considered using labels instead of comb boxes, but I'll need to be able to edit them if the record already exists.
I have some VBA code that generates a query and saves it. The query is a list of people who will be sent a communication along with the formatting for the communication. After producing the query I want to look through it and decide if there is anyone I don't want to send the communication to. I can't delete records from the query without deleting them from the database or I would simply do this.
I thought I could add a checkbox in the query that I can tick to stop a communication being sent to that person. I only want the setting saved within the query and then when I have finished it be deleted along with the query.
I have a bound form which is from tblEmployee, I'd like to have a dataset below (like a splitform but not a split as they have limits) so when i search in the box it gives me say all the smiths - i select for example david smith and it displays his information in the form objects above so they can be edited?
How to automatically populate the bound text box with data from a table for a specific entry. This is the code I wrote
Private Sub ListBox_DblClick(Cancel As Integer) Dim ListBoxSel As String ListBoxSel = Me.ListBox.Value Call proc_Update_TxtBoxes(Me.ListBox.Value) DoCmd.Close
I have a form that has a subform in it. I want the key field in the form to dictate what fields show up on the subform. When I try to set the Link child and master fields, I get a 'can't build a link between unbound forms'. How do I make this subform bound to the form????
I try to put an expression for the property "bound column" so that my lookup values will be directly dependent on one of attribute's choice
I have got four options for my attribute. Each option will use a slightly different set of lookup values. I save all four sets in another table.
Then in my properties definition I refer directly to this table as lookup base. When I put bound column number equal to an expression so as to choose the right column of lookup values it refuses. It seems that it does notl ike anything other than a numeric value.
If that's a no-go, I am not sure how to achieve what I try to do then?
Essentially I have a table that 4 attributes: QID,itemID,CatType,Catchoice
CatType can have only 4 values. Each value will make Catchoice take on a different set of combo values
The problem I am having is that I am stuck at the Catchoice lookup definition because it is dependent on what kind of CatType the user picks.
I'm trying to change the display of a bound control on a form to a linked field in another table. Seems like it should be easy to do; I just can't figure it out.
The bound column is [StudentEnrollment]![AnnualClassNumberID] and is linked to a table called AnnualClassNumbers. The field I want to display is the ClassNumber from the is the AnnualClassNumbers table.
I'm having a bit of a problem - I built quite a complex db that works great and I'm comfortable with code and access. But, I've been working with ASP for a while now and I think it has made me a bit fogetful re: Access.
If someone can point me in the right direction conceptually it would be appreciated. My system is primarily built around scheduling and I have created tables and web forms to interact with my current access database to allow our recources to check and confirm their daily schedules online.
My problem is on the Access side. I need to build a dashboard using a form that queries the table that holds our resources status information and will display whether or not they've logged in and confirmed their schedule. As well, I need controls on this form to allow me to update their individual schedules if it has been changed. Most of this is no problem, the big problem I'm having is the display of information and form interaction... Can I use a continuous form for this?
When I try to use unbound controls such as images - hiding/unhiding images depending on a specific records value (for let's say a traffic light that shows whether a resource has confirmed their schedule) - it seems that I can't control images, checkboxes, etc. for individual records on the continous form with code as all these unbound controls defualt to the value of the first record.
Hello All,I am new to Access and VB and have gained some very very useful tips from this site whilst compiling my new project.I have a question which I have been unable to find anywhere here.I have two tables at the moment. One is the main table from which my form is created. My second table is a list of registrations and the corresponding number of current hours accrued by the registration (eg ABC has 512 hours used)I query the rego table on my main form to give me a listbox so that for each new record, the user can select the rego, and the starting hours are automatically filled in the 'Start hours' field on the form (using an after update event to set the hours after the rego is selected), which is bound to the 'Start Hours' Field on the main table, so that when it is saved, the info saves to my main table.When the user returns, they then enter their Finish hours, and this finish hours is then written to the rego table, so that next time the rego is selected the start hours is the same number as the finish hours from the last time the item was used.Now for the problem. Users can fill out some parts of the form, and take it before the finishing hours are entered for the previous record. Therefore, when they return to enter their finishing hours, the starting hours hasn't updated on the form, and it doesn't change unless they reselect the rego from the list box to trigger the after update event.Is there any way, (I have tried, Me.Refresh, Requery etc) I can get the after update event to fire in the current form automatically, ie without having to reselect the same rego again? Is there a way to just refresh the rego field so that it thinks that it has updated and triggers the after update?I may have now made it clear as mud, but this is my first post so any help would be very much appreciated. I can elaborate on any problems if needs be.Thanks in advance for your help!
1. The code below should equal the Current Month 2. It does not close the form if no data.
PHP Code: If DLookup("[CurMonth]", "tblEmpEvaluation") = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmmm ") DoCmd.OpenForm "frmEvalNotice" End If
PHP Code: If DLookup("[CurMonth]", "tblEmpEvaluation") <> Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mmmm ") Then 'No Data. NotWorking? DoCmd.Close acForm, "frmEvalNotice" End If
I am looking for a complete working example of a fabricated ADO recordset bound to a form. The purpose of this is to provide a selection tool for records of a table in a multiuser database.
I could do this using a table to store the selections in the db, but I want to get a tool I can reuse in the future, obviating the need for extra tables and keeping track of users and multiple copies of the application ran by the same user.
So far I can display MEMO text, all the other values of my recordset (yes they are present) generate #ERROR in the form controls.Yes I did google. Apparently I am not the only one with this problem, but the solutions range from bizarre to weird.
I have a form and subform. The main form shows some customer details, and the continuous sub form shows that customer's charity donations.The code below runs when the form opens, and binds ADO recordsets to the two forms. The binding appears to be successful.
However whatever I do I can't make the subform update correctly to show the relevant customer donations. For example, when I use the **'d lines to update the link child/master fields, I get a "Data Provider Could Not Be Initialized" error.
Code:
Private Sub Form_Load() Dim cn As New ADODB.Connection Dim rsCust As New ADODB.Recordset Dim rsDons As New ADODB.Recordset
I've msgbox'd the txtProblemID and the correct ID is being passed. Where I fall into an error is on the frmUserInformation's onLoad event which uses the ID form the frmUserInformation.txtUserID box. The error I get is "Syntax error (missing operator in query expression '[fldUserID]='."
What I've narrowed it to is the timing between the docmd.openform and the onload of the form. I've tried changing the onload to be on activate - and it just opens empty.
how to get the docmd to open the form correctly before the onload tries to fire?
I have a number of forms that are bound to recordsets as follows:
Dim rs As New adodb.Recordset
sqlQuery = "Select * from myTable" rs.Open sqlQuery, sqlCNN, adOpenKeyset, adLockOptimistic Set Me.Recordset = rs Set rs = Nothing
In Access 2003, users could open these forms and filter on basically any field by using the right click-> Text Filters functionality.
In Access 2010, this functionality appears to work (users can apply the filter and the Toggle Filter button in the ribbon shows a filter is applied), but all of the records are still visible in the form.
Any work-around that does not involve redesigning the form to be non-recordset bound?
I'm using Access 2007. I've created a table with two fields. "Novice and Recertification" as in combo box.
I put it on the form. Now the idea is when a user clicks Recertification, it shows up on the report. When the user clicks on Novice, it should be empty on report (Reason why I want to keep novice is so that we have a record of it.)
Now the challenge is I added another column, empty field for Novice and Recertification for Recertification. Thing is if I set the bound column, I select on an empty field on form and it will be empty on report. But I want the user to see Novice on the form and it must not show on the report.
On my form, I currently have a combo box that updates text box data with each new selection. However, after these text boxes for FirstName, LastName, etc. are updated, they cannot be edited. Here is the error message that appears in the status bar:
Control can't be edited; it's bound to the expression '[Form]!EmpList.Column(13)'
I'm thinking it might be a result of the data source for the text boxes being a combo box expression. Any suggestions on how to fix this? Thanks. :)
I can't make edits with ADODB recordset bound to my form.
Access 2010 linking to SQL Server 2008.
Simple form bound to a single table.
Connection string works fine.
Code is as below (cursor etc is set using enums btw).
Private Sub Form_Open(Cancel As Integer) Dim rst As ADODB.Recordset If g1OpenRecordset(rst, "tblName", rrOpenKeyset, rrLockOptimistic, False) = False Then Cancel = True Exit Sub
So I'm not new to Access but I am to 2010. It has been a bit "challenging". Here's my first question:
1.) I'm trying to search on a field by using a command button. I basically want to click the button and the following message pops up: Enter MRN.
2.) When the MRN is entered, I would like the form to filter on all records that have this MRN.
3.) In old versions of Access, I would create a Macro for this and then call the Macro in the form.
4.) I've tried the FindRecord action in the Macro but it does not work. I actually came across several actions that don't seem to be working properly (getting error messages).
5.) In my head, this should be one of the EASIEST things to do. I've done this before in several different databases. I will admit it has been a few years since I have used Access for this (ie. building forms, macros etc.). I've primarly used it to pull in a data set and then run some queries to get the data I need quickly versus using Excel.
