I have a database that lists all the books I have read. The Table "Books" holds "AuthorId' "Title" etc. I have another table that is called "Whats Next To Read" which I store the next book that I will read from that author. It also uses "AuthorId" and "Title" etc. In the form, how can I look to see if the title put in for that author in "Whats Next" does not have the same title already in "Books" table. I can use Dcount to find the title but it could be same title different author. I need same title with same author.
This is what I have so far.
If DCount("Title", "Books", "Title = '" & forms!Whatsnexttoread!Title & "' > 0 Then
Msgbox "Title is already in books.", vbInformation
End If
I've got a huge table with one column that shows a lot of duplicate info in it. How can I let it identify the unique names and "hide" all the duplicate info that falls under it e.g.
Columns: Col.1:Fruit Col.2 Col.3 Apples A 1 Pears B 2 Peach C 3 Apples D 4 Apples Apples Pears Pears Pears
I would like the table only to show the say Fruit column with Apple, Pears,Peach and list when you click on it all the related data to it. Along with this I would like to associate all this info in the form. If I select Fruit in the form the related data should appear below in maybe a subtable or something which I should be able to select from which info I need.
I just copied a form from one database to another and for some reason the copied form in the new database does not display. the form opens but the area with all the info is blank... When I choosed design mode it all shows up correctly but in form mode it is just blank.
I have two table. Deliveries Created (DC) and Goods Receipt (GR).
I have a query which links the two by PO number, PO Item, Unit code and Delivery quantity.
This works fine until one PO Number has more than one delivery quantity which is equal. For example.... The PO quantity is 4,000 but, because the units can only be shipped in quantities of 1000, there are four different entries in both the DC and the GR tables each with the same PO number, PO Item number, Unit code and delivery quantity (and shipped on the same date). This of course gives 16 results for the query using this PO number.
There is one unique field in the DC table this is the posting number field. I've tried grouping by this number and taking the First values from the GR table. This works OK in that it produces Four results but, two of the deliveries were received on Day 1 and two were received on Day 2. If I take First of GR date I get DAY 1 for all, If I take Max I get Day2 for all. What I need is the correct date.
I will explain this situation using a scenario. Let's assumed that i'm working on a library system where i need a loan form that will only show books that are available for loan. In this case, this form has a main form that shows library member's details and its subform shows the details of the books that are loan by library member. In this subform, one of the fields (ISBN no) displays its values using a combo box. Once a value is selected from this combo box, values in other fields found in the subform will be shown too. These values are based on a table that contain books info, where only the value (ISBN no) in the combo box comes from a query. This query will only show books that are available for loan.
If a library member wants to borrow 'booktitle1' then this book will not be available to be loan by other members and assumed that there is only one 'booktitle1'.
I tried to update the field manually by changing the status field every time the book is loan out. I hope to solve this problem in an effective way.
I am new with access and i have difficulty to explain it in a much better way. Sorry, if my description cause any sort of confusion.
These are the tables.
book_info (table1) ------------------------------- bookTitle ---------- text ISBN(pkey) ------- text authorName ------ text category ---------- text dateReceived ---- date publisher ---------- text status -------------- text
user_info (table2) -------------------------------- name --------------- text userID(pKey) ------ text address ------------ text tel ------------------ number hp ------------------ number occupation -------- text DOB --------------- date
loan_info (table3) ------------------------------ userID(fKey) ------ text ISBN(fkey) -------- text dateReturned ---- date dateBorrowed ---- date dateDue ---------- date remarks ----------- text
I have an user imput form that has a unique file reference number which is typed in by the user in the first bound field. Does anyone know of a way of informing the user that the file reference number is already in use and therefore a new one must be used. At the moment the user is completing the entire form before being made aware that it would create duplicate records and therefore unable to save current record etc...Totally fustrating! any ideas? Steve
I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records.
Having a problem where information from staff worksheets has some constant data and other info is static.
I have added file for your viewing. The Propagation form 1 is the original form I was using to input the data, this was time consuming. I am trying to upgrade the form so that constant data can be entered in once and static data entered onto a sub table within the for.
Problem is that when I view the table to make sure everything is working the constant data is only recorded for one record not the balance(pls go to last record to see problem). ie date is not repeating in propagation table
Can ANYBODY help with this as this problem occurs in other forms as well.
I have a form with two fields, work_email and home_email. If after entering work_email, I would like to click on a check box if the home_email field is going to be the same information and have it copy what I entered in work_email into home_email.
On my form I have a tape Textbox where I put tape numbers in that is on a other table inside my access file (for now I just type a tape number in), then save the info to other table also inside access file.
I want to know how can I make it that if I put a tape number in the textbox, it must look at the table for tapes, and if the tape number is not in the tape table, it must not let me use the wrong tape number that is not in the table for tapes.
I have a database which has been in production for quite a few years, it even made the swap from .mdb and .accdb without losing any of the functionality. Well I just had to export from access to new access to start a new file for one of our projects and now one of the subforms is not adding information into the table. The way the subform functions is it feeds information to the table Lease Tracts, at the same time it has another table Xrf Property Tracts Leases (I didn't pick the name and it drives me nuts too!)
When a user starts to add information into the subform the table lease tracts generates an auto number "tracref" which it adds onto the line where the user is inputting the rest of their information. The relationship is set as all alike between xrf and lease tracts to input in both tables, well the xrf lease num1 is being filled in by the master/child setting but the lease num1 on the lease tract is the only field not autofilling.
I have a form "FrmMaintenance" based on the query "QryMaintenance" that is taken information from the table "TblMaintenance".
In the form, I have to pull the serial number of a machine from a drop down list which works perfectly fine in the form and it also updates other associated records.
When I go to the table, it puts associated number instead of the serial number that I've pulled.
Same thing happening for the pull down list "Engineer".
I've tried to look at the format of the "SerialNo" and changed to text, then to Combo Box but it didn't work, and the same for "Engineer"
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.
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT COLUMN in UPDATED HEADCOUNT - EMPID FORM CELL user will input an EMPID - newEMPID FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
table name : Schedule Field 1 = Vessel code Field 2 = Voyage Field 3 = ETA Field 4 = berthed Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
I have created a form based on a query that prompts the user to enter a persons name. Once entered, the persons data appears on the form and some other related data appears in a subform. However, I have found a problem that occurs when a name is entered that doesnt exist on the database... the form opens completely blank!! even text and graphics disappear!
How can I stop rounding of a number when it is written to a table ? The "Data Type" is "Number" and defined as "Long Integer", "Standard", and "Auto". The number appears with two (2) decimal places on the form, but when inserted into the table (using the SQL "INSERT" statement), it's rounded up (i.e. 34.75 to 35.00).
Hi all, I have a form that i use for data input, I have limited all of the menu's how ever the print option is still available. This leads to muppet users printing out the form and using it. There is a print button on the form that prints a report out that lays out the form into a useable item.
So the question is... How can I stop people being able to print out the form?
Everytime I change the picture of an image, a duplicate is made. I can see this in the "Insert Image" menu. It this moment, I have over 6000 duplicates which slow my form down alot. I know that you can delete these duplicates by accessing the system tables, but is there a way to stop this automatic duplication? An example is a button that makes an arrow change direction. Every time you click the button, a duplicate of the picture gets saved.
I have created some control buttons using the built in functions of Access2007 and then using the script supplied here modified the control.
So far the controls can add new record, save record, exit form, open next form and close existing form, open previous form and close existing form.
My next question is when the user reaches the end of the form fields how do I stop the form from automatically starting a new record?
For example, I use the TAB key to move from field to field and when I tab out of the last field the form goes straight to the next empty record.
I want this to be a deliberate action performed by clicking the Add New Record button and not an accident of a key press.
The other thing is, how do I get the form to open with blank/empty fields and not at the first populated record? Some of the data is updated frequently so the records need to be able to be edited but I need to prevent accidental editing of the existing records.
I am using Access 2013. I have a form with 2 subforms, each time the form is opened, the first record on both subforms are selected. Is there a way to turn off the record selector so no records are selected when the form opens?
