Microsoft Access
HELP

Sources for Quick Answers

Microsoft Access
Microsoft Access site

Google
Advanced

Questions from Users

Topic

Question & Answer
Data >How can I add a record simultaneously and automatically to two tables in a One to One Relationship?  I have enforced referential integrity, so it can cascade update and cascade delete automatically, but not add.

Relationships between tables only apply to existing records.  There is not "cascade add" for records.  You will have to put in some code.  Here is an example of how it can work.
Data >My Zip Codes have only 3 or 4 digits to them, especially in Connecticut and Massachusetts

During the data conversion, the Zip Code field was probably set to Numeric instead of Text
Here is how you can convert numeric to text and fix the zip codes
Locking > Only one person can get in to the file, and not two or more at the same time.

Here are the settings:
Default open mode: Shared
Default record locking: Edited record
Not Open databases using record-level locking
Data

Question: How can I see a directory of tables with their record count?

select [Name], DCount("*","["&[Name]&"]") as RecCount
from MSysObjects
where Type=1 and [Name] not like "Msys*"
order by 1
Data Question:  How can I find how many fields there are in a table?   It is too much trouble to count one by one.  There must be a faster way.

Go to Tools, Macro, Visual Basic Editor or Alt+F11
Select View, Immediate Window or Ctr+G
CurrentDB.TableDefs("tablefoo").Fields.Count


 
AutoNumber

Question: How can you make the AutoNumber to be in a certain sequence:

1. Make a field called ID and assign the numbers you want.

2. Copy the Table Create a new Table called TEMP with just the Structure Only, no data records

3. In TEMP add a field to the bottom of the list called IDAuto and make it AutoNumber

4. Go back to your table and sort by ID, then Copy All Records

5. Go to TEMP then Paste Append.

6. Verify that the data goes in the order you pasted and the IDAuto is in the same order as ID.

7. Go to Design mode and move the IDAuto field up to the top of the list of fields.

8. After verifying that TEMP is correct you can rename it to the name of your original data table.

Printing How To Print A Single Record from a Microsoft Access Form into a Report
You may, at times, wish to only print the record that you are currently displaying in your Microsoft Access form. This can be done directly from the Form's File » Print Option and choosing Print Range » Selected Record(s).
However, forms are not really optimized to print out records and you may wish to present your data in a more user friendly approach.
Question » From a command button on my form, is it possible to print a report of only the current record?
Answer » Yes, You will need to define a WHERE clause in the DoCmd.OpenReport method

Syntax:
DoCmd.OpenReport reportname [, view][, filtername][, wherecondition]

For Example:
DoCmd.OpenReport "rptEmployeeDetails", acViewPreview, , _
"[lngEmpID]=Forms!frmEmployeeDetails!lngEmpID"

The first section DoCmd. OpenReport "rptEmployeeDetails",acViewPreview can be broken down as:
DoCmd.OpenReport is opening the report - "rptEmployeeDetails".
acViewPreview is opening the report in Print Preview mode.
The last section enclosed in the double quotes is the criteria which can be broken down as:
[lngEmpID] is the name on the unique fieldname of the record that identifies the record you want to print - it could be the Primary Key.
Forms!frmEmployeeDetails !lngEmpID is the place where the value of lngEmpID is to be found. In this case, On a form called 'frmEmployeeDetails' in a control called 'lngEmpID'
This method assumes that the record has already been selected on a form prior to printing. The Report name here is "rptEmployeeDetails".
 
eMail Connect Access to Outlook contacts
https://www.youtube.com/watch?v=UOq1CiWIqN4
eMail Richard Ross send email from Access to Outlook
single or mass emails
https://www.youtube.com/watch?v=s-Z0LHgipVE
 
Fields

How to overcome the 255 field limit in Microsoft Access
You can make a related table with the same Primary Key field, such as ID.  In the parent table, make ID an AutoNumber, and a Number (Integer) in the Child table.  Do not make them both AutoNumbers.  Make a relation with Referential Integrity on, with Cascade Update and Cascade Delete.
Use a custom Form only for appending, which is what the users will usually use.  When they click to Append a new record, the Form will trigger an AfterInsert code for After Insert of a new parent record:
(You can insert 3 variables for fMyParentForm, MyChildTable, and MyUniqueID.)

Private Sub Form_AfterInsert()

'Add record to Child table after adding ID to Parent table
Dim frmParent As String
Dim tblChild As String
Dim fldId As String
Dim nId As Integer
frmParent = "fMyParentForm"
tblChild = "MyChildTable"
fldId = "MyUniqueID"
nId = Forms(frmParent).Controls(fldId)
If 1 = MsgBox("Add to " & tblChild & "." & fldId & "=" & nId & Chr(13) & "(recommended)", vbOKCancel) Then

Set recChild = CurrentDb.OpenRecordset("select * from " & tblChild)
recChild.AddNew
recChild![ID] = nId
recChild.Update
recChild.Close
Set recChild = Nothing
Me.Refresh

Else

MsgBox ("No Change")

End If

End Sub

https://stackoverflow.com/questions/39109270/how-to-use-vba-to-add-new-record-in-ms-access/77553717#77553717

Experien 888-397-3742
Equifax 800-525-6285
TransUnion 800-916-8800

Return

.

C I Corporation
202-829-4444
800-319-3190