What I am trying to create is a Despatch database for our warehouse.
Records in table:
Date ()
Customer
Invoice #
Qty of parcels sent
Courier used
consignment number
In most cases we will send one invoice per consignment number (database works fine for this) But on occasion we may send multiple invoices. What I want to be able to do is have a list box to select the number of invoices, this will make available additional fields for Invoice # and Qty of parcels sent. The idea is to get away from keying in the other records for each invoice going to the same place.
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued 0001 01/11/2013 Gloves (Orange) 10 0001 01/11/2013 Hard Hats (Black) 2 0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.
Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.
For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.
I need for a valued to be changed in a row after it checks for how many rows have another value, counting how many and that number making it the amount field.
Example: Fields- Name sponsor amount
I want access to show how many people have been sponsored by Gabriel... So if 3 where sponsored by Gabriel show 3 to the amount field for Gabriel row! Any way to do this or an easier way?
I for it to check it every time a user is added incase the user added is sponsored by Gabriel add it automatically!
I wrote code that should validate a field when entering a new record and then if a condition is true, that new record should be cancelled and not entered into the table.
I managed to partially achieve this by writing the code below, but the new record does not get cancelled because the table will still create a PK for that record and leave the rest of the fields empty. I am using an autonumber for the PK that's why the table creates it automatically What I want to achieve is to cancel the creation of a new record at once, I don't want even PK created for that new record.
I used the CancelUpdate because I thought it would cancel the record creation, but it did not! When I read about it it said that I need to use it with either Edit or AddNew, (which i don't understand why!) but it still does not work.
Private Sub PlotNum_BeforeUpdate(Cancel As Integer) On Error GoTo Err_msg Dim db As DAO.Database, rs As DAO.Recordset Dim n As Integer, i As Integer Dim vPlotNum As Integer Dim vPhaseID As Integer
Set db = CurrentDb Set rs = db.OpenRecordset("tblHouse") rs.MoveLast n = rs.RecordCount rs.MoveFirst If n > 0 Then For i = 1 To n If rs![PhaseID] = vPhaseID Then If rs![PlotNum] = vPlotNum Then rs.Edit rs.CancelUpdate MsgBox "This plot number already exist in this particular phase." & vbCrLf & "Please choose a different Plot Number" Forms![frmHouse].qryHouse2.Form![PlotNum].Text = "" End If End If rs.MoveNext Next i End If rs.Close db.Close Set db = Nothing Set rs = Nothing
Exit_Err_msg: Exit Sub
Err_msg: MsgBox Err.Description Resume Exit_Err_msg End Sub
Any suggestions will be very much appreciated. Thanks. B
I am doing one project using access. I 've made two forms. In one form, lets say, it contains two common buttons. Both buttons will load the same second form and will add new record to the same table. When I click first button, it will load second form with all fields blank and one field of record will autofill one value,lets say "a" to the table.That field shouldn't be appeared in the second form,just want to fill automatically. Then the other fields of new records will be filled by the user input from the second form. Also, when I click second button, it will do similarly but only the autofill value will be different from the first one. Does anyone know how to make it? If don't understand what I am saying, I can explain it again. Any help will be grateful for me.
The main table for the database I am working on contains the following fields:
ID Mfr Control Number Initial or Follow-Up Follow-up Number Suspect Date of Initial Email Date Received Date Submitted Date of Report Serious Brief Description Causality Notes
With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).
I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.
I want criteria on the "Initial or Follow-up" field to only bring "initial" I want criteria on the "Serious" field to only bring "serious" I want criteria on the "Date of This Report" field of "<Date()-"15""
Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?
I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague.
The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp.
The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query.
The second piece of code stores this information in a table called temp
I would like to be able to do 2 things with this;
1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs)
2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible.
The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp.
Any ideas greatly appreciated.
Here is the existing code …
Option Compare Database Option Explicit
Private Sub Command0_Click() Dim db As Database Dim rsRjobs As Recordset Dim rsRapps As Recordset Dim LengthofUnionSQL As Long Dim sql As String Dim UnionSQL As String Set db = CurrentDb Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot) Do While Not rsRjobs.EOF UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union " rsRjobs.MoveNext Loop 'following two lines are to remove the trailing word Union from the string unionsql LengthofUnionSQL = Len(UnionSQL) UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7) ' Now variable Unionsql will hold the value something like ' Select ObjectID, SearchNo, DateSearched, Consultant from J000145 ' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146 ' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147 MsgBox UnionSQL
Set db = CurrentDb Dim rsUnionquery As Recordset Dim rstemp As Recordset Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges) Set rsUnionquery = db.OpenRecordset(UnionSQL) Do While Not rsUnionquery.EOF rstemp.AddNew rstemp!ObjectID = rsUnionquery!ObjectID rstemp!SearchNo = rsUnionquery!SearchNo rstemp!DateSearched = rsUnionquery!DateSearched rstemp!Consultant = rsUnionquery!Consultant rstemp!jobID = rsUnionquery!jobID
Hopefully I have provided enough info as I have tried to keep it concise...
I am adding records to both a parent table and a child table using a form (for the parent db) and a subform (for the child db). When enterring data into the subform, I would like all previous records enterred into the subform (for a given entry in the main form) to remain visible as new records are entered (rather than simply having the view switch from one record to the next). A follow-up question would be...how do I get a unique identifier variable to increment one unit every time I add a new record into the subform, e.g.
I start with a table of UserIDs, names and roles (tblUserIDList). I have a simple query off that which lists them in alpha order by UserID. Next I have a simple form with a combo box which uses that query to select the User and binds the UserID field to use.
There is a button which triggers a macro which opens the 2nd form using a query which filters using that UserID. There is another point in that form in which that UserID is again used as a filter for other data. The 2nd form is a data input form. All works fine up to that point.
However, when the 2 forms are closed, for some reason the UserID is re-added to the tblUserIDList. I've never had this happen before.
Hi, I have the following situation. I have a switchboard form which has 2 buttons (Add And Edit) I have a patient record form, which has 2 fields, (DateRecCaptured and DateRecUpdated) Both buttons on the switchboard open the Patient Record form (one opens it in Add mode and the other in Edit mode)
This is what should happen. If I click on the add button on the switchboard, the patient record form should open to allow me to add a record. The system date should then automatically be saved in the DateRecCaptured field. If i click on the Edit button and edit a record, the system date should be saved in the DateRecUpdated field. If no updates are made, the field shouldn't be updated. If you scroll among records, the DateRecUpdated field shouldn't be updated. When editing, the DateRecCaptured field should remain unchanged.
I need to add an additional filter to my unbound form and can't seem to get the syntax right -
I have a text field in my form "[B Style]" that has either a "Y" or "N"
I want to add a checkbox on my main form where, if checkbox=True then [B Style]="Y"
Private Sub ckBStyle_Click() Dim stFilter As String Dim stDocName As String stDocName = "Modify_OpenItems" If Me.ckBStyle.Value = True Then DoCmd.OpenForm stDocName, , , ("[B Style]" = "Y") Else: DoCmd.OpenForm stDocName End If End Sub
i have an unbound form with the following code which works as it should
Code: Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tbl_Courses", dbOpenDynaset) With rst .AddNew rst!DateCreated = DateCreated rst!Originator = Originator
[code]....
I also need to add other text fields from the unbound form to another table but can't seem to work out how to do it.
I have a form with a tabbed area, each tab containing a subform. One of these subforms adds records to a table. Another subform shows the totals from that table.
But when I add records, the totals tab is not updated with the new quantities unless I close the form completely and go back in. I tried adding Me.Dirty = False to the subform that adds the records but that makes no difference.
It seems as though the 'totals' subform gets those values as the form is loaded and does not change, even when records are added and then that tab is selected.
How can I get the totals subform to show the updated totals?
I have a quotation database running in our company network which we use to send quotes to customers. A quote may have one or more items in it and each item has different prices based on the quantity. Each item will have a record in the database. So, if a quote has five items, there will be five records with same quote number and if 30 items, 30 records with same quote number.
Many times, same enquiries are coming from different sources (customers) asking quotes specially Govt orders. Now we need to take every enquiry as separate and feed separately in the database. Consider the time for a quote which has 30 items in it and each items has 6 level of pricing. When this same enquiry is coming from 12 different customers, it takes a lot of time to feed it in.
Now my question: How can I copy all the records belongs to one "quote no" and add it to the same table with a different quote number and some changes to the other customer related fields? If I can do that, I just need to change the customer address and I am ready with the second, then third and so on. I want to copy all related records, change quote number and related fields and then append to database.
Also I need this when I revise a quote. The system needs to keep the old quote as well as the revised quote for future reference. If I can duplicate it in one command, I just need to do the changes, Revision Number, Revision Date etc. and the revision is ready to go. Now I use update querries to do changes to all related records during revision. Then I will have only the revised quote in my database and when I want to refer the old quote, I have to refer the hard copy.
I know there is a "Duplicate Record" command available in form level (through wizard), but that duplicates only one record, not a selection of records as I need it here. If this can do with macros, fine. I am not an expert in VB, but if I get the code, I will attach it to a command button and use.
I would like to use a listbox set to multiselect to add records to a junction table. I've been using code to accomplish this with checkboxes (love how it looks and works) but after moving my tables to Office 365 as the backend, linkedto a local frontend, sql does not like this particular set up, and I do not have the time or knowledge to sort out why. So what I need is a step by step to look at the many, in this case possible roles a contact can have, and choose one or more, which then creates a record in the junction table with the contact id and role id.
I would prefer to not use a combobox on a continuous form because every time a user goes to select roles he would have to scroll through all the choices for each separate role.
I have 2 tables Master table (Jobs) containing the primary key ("Job Reference") and 2nd table (Candidates) with the foreign key ("Job Reference")
2 Forms
frmJobs Form to view job details frmCandidates form to view Candates information
I have placed add new candidate button on the frmJobs form which opens the candidate form in the add mode as a blank form. This is fine but I'm looking to add a record where it picks up the "Job Reference" text field value from the main frmJobs and update it in the "Job Refernce" text field on the frmCandidate when I click add new candidate.
How can I achieve this? I'm fairly new to access/vba
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.
So, what I want to add multiple records using a single main form.
Is is possible to?:
1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?
2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?
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
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control Dim varItem As Variant
I want to list all the records which contains data as per my filter form field company.
I have tried this
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")
problem is this i want to display all the records including null, but this code only show all records except null
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")
