I've done a query with my equipment table for duplicates company names to get a number for my customer form for total number of equipment.
how can I get the number of duplicates for each company to be put into total number of equipment in my customer form?
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.
Any alternative method of adding a search?
I have a table called Tbl_Query_List that houses the query names that I want to be displayed on a continuos form. I want the user to select the query by highlighting it in the continious form and then having a command button at the bottom of the form.
I have everything setup including the command button. Assuming that on the EVENTS tab I set the OnClick to [Event Procedure] but when I click on the ellipsis what do I type to make it run the highlighted query?
I have a form that has the following fields: Ticket Date From, Ticket Date To, Pawnshop, Property Category, Item Description, Make, and Serial Number.
The purpose of this form is to allow an unexperienced user of Access to query a database. Thus far, when I specify the query to include dates from/to, pawnshop, property category (eg, jewelry), and item description (ring) the records retrieved are the correct number. None of these fields contain null values. However, the Make field has some null values and the Serial Number field has an extensive number of null values.
My problem is that I have noticed because the Make field has some null values a few number of records aren't retrieved after my query (even if I leave the Make field blank). This problem is much worse with the Serial Number field. For instance, I ran a basic query via my form specifying the dates and property category-jewelry and got back 229 records but when I verified this with a separate query I got 960 records, which is the correct number.
My criteria from my querry are as follows:
Between [Forms]![PawnProperty_PawnerQueryForm].[TicketDateFrom] And [Forms]![PawnProperty_PawnerQueryForm].[TicketDateTo]
[Forms]![PawnProperty_PawnerQueryForm].[PropertyCategory]
Like "*" & [Forms]![PawnProperty_PawnerQueryForm]![Pawnshop] & "*"
Like "*" & [Forms]![PawnProperty_PawnerQueryForm].[PropertyDescription] & "*"
Like "*" & [Forms]![PawnProperty_PawnerQueryForm].[Make] & "*"
Like "*" & [Forms]![PawnProperty_PawnerQueryForm].[SerialNumber] & "*"
The bottom two I've had to remove because the correct number of records were not being retrieved. I suspect that the null values in the Make and Serial Number fields are the root of the problem. to make these queries all work together!!
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#dd/mm/yyyy#"
[Code] .....
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I have two tables (Access 2010). One with a list of names (List1) and another with a very similar list of names (List2), but they differ in very small ways. For example, List1 might have John Smith, and List2 would have Smith, John L.; and Smith, John. List2 also has a unique ID associated with these names that I need to append to List1.
I need to design a form that will allow me to look up names in List1, and have it return all names that are similar in List2. I then need to be able to choose with record in List2 matches with the List1 entry (based on a few other columns in List2, such as birth date) and have the form add that unique ID to the List1 record.
PS: I am using Access 2010
I'm trying to make a very simple click through data entry screen in Access 2007. For each record I want to have a standard form with three buttons at the bottom. Each button opens a new form (each form has a set of tabbed pages on it as there is lots to enter).How do I make sure that each of the additional forms populate the same record as the main form.
View 1 Replies View RelatedI have a Lost/Found property database which we use to keep record of the lost/found property and it is working fine.
As per our organization's policy, we keep the item(s) for up to three months in which if it gets restored to the owner then fair enough otherwise after three months the item(s) can be claimed by the "Finder". But for this very purpose we issue the Finder with a "Claim Receipt" which he/she should bring in when claiming for the item(s) after three months period. Therefore, It's just the right time to upgrade the database to a more professional level.
The database has one table and two forms.
One form (LostFoundForm) is visible to the user in which they enter data, this form has two sections; Item(s) & Finder's details and the second section is about Restoring details.
However the second form (ClaimReceiptForm) is hidden to the users (for manual data entry) and has only one section which is exactly the same as the first form's first section, i.e., Item(s) & Finder's details. This second form takes the data automatically from the first form because the table behind them is same and fields are same (please see attachment). Up to here it's all working fine.
I would like to introduce a Checkbox or a CommandButton in the LostFoundForm which when we click should pull up a msgbox asking "Do you want to print the receipt for this item(s)?" with a Yes/No option. On clicking "No" it should, obviously, settle down but on clicking "Yes" it should print the "corresponding record" from the ClaimReceiptForm.
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.
I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.
The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.
I have a data input form with multiple combo boxes. The combo boxes are related to their own separate tables. The Row Source on some of the combo boxes are from queries with concatenated fields.
Not sure the best way to be able to quickly/simply input new values into the appropriate tables from here.
I've attached a screenshot of my form.
I am creating a production type database with different stations (assembly 1, assembly 2, etc) with a master parts list that has every part. I want to cross reference each station input with the master parts list in order to make sure the part is in inventory. Can I create a button or to do this on the form? Or is there a better way? I would cross reference two fields.
View 1 Replies View RelatedI'm a relative beginner at VBA and I'm trying to use a form to update a table. The form is used to get an agent name, start and end date. When they hit the "update" button, I want to go to the table, select all records where the agent name (called Fullname in the table) matches and the field called "event_date" is between the start and end dates. I have the below code, which isn't working.
Set rst = CurrentDb.OpenRecordset(Table)
rst.MoveFirst
Do While rst.EOF = False
If rst.Fields("Fullname").Value = Me.Agent And Me.StartDate <= rst.Fields("event_date") And rst.Fields("event_date") <= Me.EndDate Then
rst.Edit
rst.Fields("CompleteDate").Value = DATE
rst.Fields("Comments").Value = "Autofilled"
rst.Update
End If
rst.MoveNext
Loop
rst.Close
I have been developing a form that will allow me to input, edit and delete data in a table.I have the input, delete, clear, and close command buttons working ok, but I am having trouble getting the edit function to work.
What happens is that I highlight the line in the table (shown on the form as a sub form) click the edit command button, and the field are populated with the current data. I then edit the field that I want, and the add, button, changes to Update. When I go to update I keep getting an error message "Cannot update 'ID'; field not updatable.Below is the code I am using, when I go to the debug the arrow points to the "WHERE ID=" line.
Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
[code]...
I am having an interesting time on a project. I am trying to create a small inventory management database with BOM (bill of materials) control.Today I am working on how to create the BOM's. In case your not familiar with inventory management, a BOM is basically a list of items and quantities used when creating a product.
Since it is possible for a single BOM to have over 200 Items associated with it, I can not have a single table with all my BOM's (fields max at 255)
So I was thinking I would be able to have a Master table with the BOM list and then create individual tables for each specific BOM.I am not 100% sure how to make this all work together but I am struggling forward on my quest..
I was hoping I could create a form for BOM management, (create/edit/delete) but since I can not keep the BOMS in a single table I would need this form to create the new table.
Is it possible for a form to create a table? or do I have to create the table first than have a specific form per table to do the editing? or perhaps a drop down selector to choose which BOM/Table I am working on.
I am having an issue in access form that when I enter new values in a form it does not appear on the table. Normally, as I understand, value entered in form should appear on table automatically. This is not happening to me.
I checked the design view of the form and find that the data is linked to table, but in reality it is not the case.
Problem Solved!!!!!!!!!
View 3 Replies View RelatedMay not be using the correct terminology here. I have a form that currently does not have a RecordSource. From this form, I want to create a table called "ICEA" with the following fields (which would be entered from the form): OperatingDay, Hour_Ending, Import, Export, and Net.Can this be done with a SELECT statement or does it have to be done through VBA?
View 4 Replies View RelatedI'm creating a form for orders. In this form I use a combo box to select a product from a table called "Products". In this table there is also a second column with the "PricePerUnit" How can I insert the PricePerUnit from the table Products into the table Orders when I select a product with the combo box?
View 6 Replies View RelatedI have a login button in which if the username has an empty password or the user is his/her first time to login, another form will appear which is the Set New Password. How can I change the password of that current user, my Set New Password Form has two fields which is 'txtSetPassword1' and 'txtSetPassword2' and an OK button.
The user must enter a new password and a verification password, if the 2 fields matched, the new password entered will change the password in the table. The table name is 'tbl_UsernamesQry'.
I have done it but the main problem is, it changes the other's users password not the current user.
Here is my code for the OK button of that form Set New Password:
Private Sub btnSetOk_Click()
If Me.txtSetPassword1.Value = Me.txtSetPassword2.Value Then
Me.txtSetPassword1.SetFocus
[Code].....
I have a form with two unbound text boxes: HireMovieID and HireCustomerID, and a button HireButton which runs my query: HireHistoryQuery. Then I have a table: HireHistory. In my HireHistory table, I have my CustomerID's along the top as column names. Then the records for those columns are in this sort of format: "0001 on 19/05/2006" as type Text.How can I make it so that when a user enters a Customer ID (e.g. 23) into my HireCustomerID box in my form, it shows column number 23 and all it's records?
Also, what code do I need so that if someone enters something into the HireMovieID text box in my form (e.g. 0001) it shows all of the instances of that from the whole table in its respective column?
I have a search form that searches for student ID from the student demographics table. I have it set when I click on the student ID (Frm_Student_Demographics_DailyCares) it will open the student visit form. When the student visit form opens it will show the student ID on the Student Visit Form but it is not being recorded in the student visit table.
I have Student ID control source on the Student Visit Form set as:
=[Forms]![frm_SearchStudent_DailyCares]![Frm_Student_Demographics_DailyCares].[Form].[SD_Record_ID].[Value]
My tables are called Student Demographics(parent table) and Student Daily Cares(child table) and they are linked by Student ID.
I have done this before on another part of this database but I just simply can't seem to find what I missed.
Here is a sample of my forms and tables.
I have a form that I need to add a new record using embedded SQL rather than just binding the form and using an automatic new record. My form will display the needed random number in a textbox and that needs to be my new record number. So I need first to check to see if that number already exists as a record number then add it if it does not and either way insert or update a set of additional fields. Something like:
DoCmd.RunSQL "SELECT * FROM tblExceptions WHERE ReconciliationID = " & Me.txtReconciliationID
Psuedocode: IF Record Exists UPDATE Field1 and FIELD2
Else Create record and insert VALUES into Field1 and FIELD2
End If