AutoFormat Tables Upon Insert

Dec 22, 2007

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?

View Replies


ADVERTISEMENT

AutoFormat

May 17, 2005

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.

View 1 Replies View Related

Delete AutoFormat ??

Apr 7, 2006

There's got to be a simple way to remove an Autoformat from a form. Right? I can't find it...

View 3 Replies View Related

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

Aug 28, 2014

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.

View 1 Replies View Related

Sql For Insert Into Join Tables

Nov 6, 2007

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 ?).

Thanks and regards,
Prodigy.

View 1 Replies View Related

Insert New Records In Ref Integrity Tables

Sep 6, 2006

Hi All,

I hope somebody can help me on this.

I still use Access 97.

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.

Thanks in anticipation

Richard

View 2 Replies View Related

Insert And Delete Link Tables Using Vba

Jul 11, 2007

I have two table which is c:db1.mdb; c:db2.mdb

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?

View 1 Replies View Related

Insert Values Automactly In Tables

Nov 22, 2006

Hi, this is my first post in this forum... so thanks all for your answers...

Ok, the problem is this...

I've got a database with some tables, but in that database i've got 3 important tables....

Lets refer that the tables have this names [Table1,Table2,Table3];

In the Table 1, there are 3 important variables, one of them its a primary Key, ok all i wanna know how to do... its this:

When i insert a new regist in this table, the first 3 values from the first three colunns must be replicated to table2 and table3.

Can you help me PLEASE!! :(

View 10 Replies View Related

Insert Into Statement With Tables From 2 Databases

Dec 3, 2005

Dear members,

I have two databases, Source and destination ;

strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"

StrDestDB = "C:DBWSS_Khatlon.mdb"

I would like to append records to the destination table (Contractor_GIS) when there are no matching records in the source table (Contractor).

My code is as follows:


Private Sub CommandButton1_Click()

Dim strTemp As String
Dim strSQL As String
Dim StrDestDB As String
Dim strSrceDB As String
Dim mdb As DAO.Database

On Error GoTo ErrorHandler
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"

'Make sure it is there
If Dir(StrDestDB) = "" Then
Call MsgBox(StrDestDB & " does not exist", vbOKOnly, "Aborting...")

ElseIf Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
Else

strSQL= "INSERT INTO Contractor_GIS("
strSQL = strSQL & "System_ID,"
strSQL = strSQL & "Contractor_Name)"
strSQL = strSQL & "IN '" & StrDestDB & "' "
strSQL= strSQL & "SELECT Contractor.System_ID,"
strSQL = strSQL & "Contractor.Contractor_Name "
strSQL = strSQL & "FROM Contractor IN '" & strSrceDB & "' "
strSQL = strSQL & "Contractor_GIS IN '" & StrDestDB & "' "
strSQL = strSQL & "where Contractor.System_ID <> Contractor_GIS.System_ID;"

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 get Syntax Error in "Insert into" clause.

Any help on this will be most appreciated.

Thanks in advance.

Irshad

View 2 Replies View Related

Tables :: Insert A Calendar In A Field?

Sep 23, 2013

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).

View 1 Replies View Related

Insert New Record In Ref Integrity Linked Tables

Sep 6, 2006

Hi All,

I hope somebody can help me on this.

I still use Access 97.

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.

Thanks in anticipation

Richard

View 1 Replies View Related

Insert The Records Into ODBC Linked Tables

May 25, 2007

Hi,

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

which way is rather faster?

View 1 Replies View Related

Insert Data In Two Related Tables Simultaneously

Dec 11, 2004

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.

View 6 Replies View Related

Tables :: Double Dlookup Insert Button

Apr 13, 2014

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?

View 6 Replies View Related

2 Tables Insert Only New Records In Tbl1 Help Wanted ASAP

Sep 3, 2005

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.

View 2 Replies View Related

Insert Microsoft Access Tables Into Excel Spreadsheets

Sep 4, 2007

Hi Folks,

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..

Is this possible.
please help...

Thanks in advance.
Jag

View 4 Replies View Related

Tables :: How To Insert Text Box Value To First Empty Cell In Table

Mar 17, 2014

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.

View 14 Replies View Related

Modules & VBA :: Insert Statement Not Saving Data To Tables

Jul 16, 2013

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

[code]...

View 11 Replies View Related

Tables :: Unable To Insert Multiple Values Selected From Listbox To Database

Nov 19, 2012

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

[Code]...

View 10 Replies View Related

INSERT Query - Insert New Data Only

Jul 2, 2010

Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.

TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }

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.

View 2 Replies View Related

Queries :: Insert Into One To Many PK And FK Insert

Mar 25, 2015

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.

View 2 Replies View Related

Db Sql Insert

Dec 19, 2006

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.

View 2 Replies View Related

Insert Into??

Jun 10, 2005

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?

Thanks in advance!
Tim

View 1 Replies View Related

Insert All

Feb 27, 2008

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.

View 14 Replies View Related

Help With SQL Insert

Mar 26, 2006

Hi,

I need urgent help. I have a question to ask and wonder if anyone might be able to help me.

My question is how do i execute SQL insert into a table called Job Register in access.

Here is my code before update

If foundflag = False Then
'new job detected
StrSQL = "INSERT INTO [Job Register](Industry_No, Client_No, Job_No, Job_Name, Job_Contact, Job_Phone, Job_MPhone, Job_Fee, Job_Reference, Job_Manager, Date_Registered) "
StrSQL = "VALUES ('" & Me.Combo20 & "', '" & Me.Text2 & "', '" & Me.Text4 & "', '" & Me.Text8 & "', '" & Me.Text10 & "', '" & Me.Text12 & "', '" & Me.Job_Mphone & "', '" & Me.Text14 & "', '" & Me.Text16 & "', '" & Me.Job_Manager & "', '" & Me.Date_Registered & "')"

End If

Your help is greatly appreciated.

Thank you very much in advance

View 3 Replies View Related

Insert A Row

Sep 18, 2006

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?????

Any suggestions greatly appreciated.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved