Is there any way to change the color of a cell based on the information given. For istance if the data in the cell is "yes" background is green if it's "no" read and if it's "maybe" then yellow?
also if this is possible and i do a report on all the answers would they appear in the report colored like so?
I thought I would "jazz" up some of my dull old forms yesterday & started out using the AutoFormat button on the Access toolbar. It is a nice feature but I noticed that after completion of one or two small forms the size of the Db was starting to blow out. While the "bloat" wasn't too big I suspect that if I were to dive in and "jazz" up all the old forms I would see a big increase in Db bloat. Is this common when using the AutoFormat feature? Like many good features in Access I am guessing it comes at a cost.
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
Hi, I have an unbound form that is based on a query. On the Load-event I have populated textboxes with the underlying records (it works fine).
The underlying query is based on 2 tables that are joined on (PrimaryKey) Table1.ID = Table2.ID (ForeignKey).
The Form has one Add-CmdButton. When the user will click the Add-button the content of the textbox will get added or saved in the underlying database.
I want to know the SQL to add, so that I can write the code behind on the Click-event of the button.
I know on how to add records with a single table (writing code behind), however, I want to know on how to do when it comes with joined tables (INSERT SQL statement ?).
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
The user is currently using c:main.mdb In main.mdb, I have a form, and have a combobox with two value, which is db1.mdb and db2.mdb.
If the user choose db2.mdb, delete all the current db1.mdb link tables(if there is any), and import all the tables from db2.mdb as link table If the user choose db1.mdb, delete all the current db2.mdb link tables(if there is any), and import all the tables from db1.mdb as link table
And I need to perform some vba on the afterUpdate event of combobox.
My question is, how to 1)delete all link tables using vba 2)Import all tables from a certain mdb file as linked table using vba?
Set mdb = DBEngine.OpenDatabase(StrDestDB) Debug.Print strSQL Call mdb.Execute(strSQL) mdb.Close Set mdb = Nothing DoEvents MsgBox ("The Geodatabase tables have been successfully appended!") End If Exit Sub
ErrorHandler: strTemp = Err.Description & " [Update_SystemTab]" Call MsgBox(strTemp, vbCritical, "Contact Help Desk") End Sub
I am creating a new system over the course of the year for a chosen organization. I have chosen to do Zumba (dance classes). I have created 3 tables for my system; "Bookings", "Classes", "Client Details".
Now... In the table "Classes", I have got Class ID (eg. HFP), the class type (eg. Zumba Sentao), the Venue (eg. Village Hall), postcode and class dates.
This is where I am stuck, in the fields of Class Dates I want to add a calender which allows me to enter the dates of eg. Zumba Sentao throughout a 2 month period. I want to be able to insert a calender icon/button into the field and then show all the dates over 2 months when this class is on (and then grey out all of the dates that aren't applicable to this class).
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
I have a lot data to append to ODBC linked table in MS Access. I want to know that which way is faster to append the records.
if I append the data into ODBC linked table, 1) create the one query (append) to insert the records into ODBC linked table 2) use the VBA code (DAO/ADO) to insert the records into ODBC linked table
I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form? Thanks for your help.
i already make this sql for dlookup table for may insert button, all going good, but when i'm going to make double command with different msg box for different criteria, it going fail
1. This my Working code:
If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then MsgBox "Grant Number Invalid", vbOKOnly Me.[cbostatuspembayaran] = "Geran Negatif" CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..) End if
2. This my not working code:
If Me.txtidborang.Tag & "" = "" = (DLookup("NoGerankod", "HutangKeseluruhan", "NoGerankod='" & Me.txtnogeran & "'")) Then MsgBox "Grant Number Invalid", vbOKOnly Me.[cbostatuspembayaran] = "Geran Negatif" If Me.txtidborang.Tag & "" = "" = (DLookup("NoMatrikkod", "HutangKeseluruhan", "NoMatrikkod='" & Me.txtukmper & "'")) Then MsgBox "This Student still have debt ", vbOKOnly Me.[cbostatuspembayaran] = "Geran Aktif"
CurrentDb.Execute "INSERT INTO Januari (bla..bla..bla..)
End if End if
Q: How can i combine two dlookup together for different msg box?
I have two tables tbl1 and tbl2 tbl1 has 10 fields named tbl1.id tbl1.field2 tbl1.field3 tbl1.field4 tbl2 has only three fields tbl2.field1 tbl2.field2 tbl2.field3
Now i need to insert values into tbl1:: tbl1.field1 tbl1.field2 tbl1.field3 from tbl2.field1 tbl2.field2 tbl2.field3 respectively, but i need to make sure if tbl2.field3 value is already there in tbl1.field3 then we don't need import those records. so we only need records if value of tbl2.field3 is not already there in tbl1.field3.
Please let me know What statement do I need to write so i can import all data from tbl2 into tbl1 by comparing as above.
I have MS Access database with 5 tables in it. And the are few people on my network using excel spreadsheet, which i populate manually everyday from MS Access tables.
Looking for any way i can update the tables and spreadsheet data will will updated as well. and i want to use query in excel to filter data..
I have a simple form (frmAddPaper): txtPaper where user enters name of new newspaper, and cboCity, where user selects the newspapers town.
Also Close and Save buttons.I also have a table, tblCity. Columns as follow: CityID, City, Paper1, Paper2, Paper3, Paper4, Paper5, Paper 6.
Some cities have values (Newspaper names) in just Paper1 field. Others in Paper 1 & Paper 2, and some in Paper1, Paper2 and Paper3.I want to add the txtPaper value, to the first empty column, in the row where cboCity matches City column.
I am trying to create a form in which users can save new supplier data to a database. I am using two insert statements which insert similar data in to two similar tables. The insert statements appear to have no problems and no errors are produced upon execution yet the new data doesn't save into the tables. Here's the code;
Code: Private Sub Command14_Click() Dim sqlstr As String Dim dbs As Database
i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.
i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.
(ps: in my property sheet for my lstCuisine listbox its multi select is simple)
Here is my codes:
Private Sub Add_Record_Click() If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name" Else DoCmd.GoToRecord , , acNewRec End If Dim conceptValue As String
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I have a question.....i am uing Access as the back-end database of a VB6 Front end app. Basically i have a recordset with about 300K records....i currently Insert 1 row at a time....which takes ALOT of time.....is there a faster way to do the insert? i saw some things on the internet about a BULK insert....but it sounds like it is only with SQL Server or ORACLE. Please let me know what you think.
I have a 2 identical databases. One named customerrent and the other customerhistory. Both databases contain related tables. tblcustomer tblrent What i would like is a button on my form to archive the current record, to the customerhistory database then delete that same record from the customerrent database. Can someone give me an example how to accomplish this?
What would be the best way to do the following scenario:
I have a form where users can select continents. Another combo box is autofilled to show the respective countries. In that same combo box there is also an "All" option. So for North America the combo box would be filled with U.S., Mexico, Canada, and All. There are many other text/combo boxes that the user fills in and then finally a table is update with the record.
I want to be able to insert into a table identical records for U.S., Mexico and Canada if the user selects "All" in the countries combo box.
I have a database that is tracking proposals and job orders. First the proposal goes out and then sometimes the customer what to change the order in some way which means i need to put a new row somewhere other then the end of the record. I know in Access you can not add a row in the middle of a table but everyone was use to doing this in Excel and was able to make such adjustments. I thought about adding a Sort column so the data entry person could add their own sort order but some proposals can be hundreds of lines and no one wants to do this, however they want to be able to make changes and adjustments to the existing proposal. Is there some magic I could create in the development of this database to make everyone happy?????