Full Version: Forms
UtterAccess Forums > Microsoft® Access > Access Forms
Good night Everyone!
I am trying to create a form that would be used to update a table. I have a couple of problems in doing this. First, in my form I am having problems in displaying phone numbers in my listbox. Second, I am trying to create this form so that a user can update the Phones table by linking the campaign it is to be associated. Finally, since the database is historical I would also like to update another table with primary keys from the phones and campaign tables.
Please help. I am open to all suggestions. The db is attached.
Many thanks,
there is a problem with the zip file you attached -- cannot extract...
database attachment to a UA post has to be:
1. zipped up
2. < 500K
before you zip the file, do Compact/Repair (always do this before you post)
from the menu: Tools, Database Utilities, Compact and Repair Database
if your database is too big to post:
1. make a blank database
2. import the objects necessary to load the form, run the query, open the report, etc and all source objects that pertain to your question
File, Get External Data, Import...
Compact/Repair, then close the database and zip it up
if the file is STILL too big...
1. make a blank database
2. import the objects necessary to load the form, run the query, open the report, etc and all source objects that pertain to your question
File, Get External Data, Import...
on the tables tab of the Import dialog box, click the Options command button and choose 'Structure Only'
3. then create a few sample records in each table
then Compact/Repair, close it and zip it up
Thank you. I will repost following your advice.
I have reattached the form as advised. The file is only 200kb.
Larry Larsen
It would seem your duplicating your data with regards to the status..
Table tlkpStatus holds the word "status"..!!
Table tblPhoneNumbers also holds the word status..!!
The above setup means your duplicating data.. data should only ever be in one place and it's reference id # is what 's duplicated. This way you only have to change details at a single source table and not for each record you have captured..
Remove that format for the phone number at table level and apply the:
PN: Format([PhoneNumber],"000-000-000")
at form level within the sql for the listbox..
Hi CCSlice (what is your name?)

currently, your InputMask for Phone Number is:

this is NOT storing the symbols, so a list box will just show the numeric data. If you want the database to store symbols in the mask, you must specify that.

The InputMask property can contain up to three sections separated by semicolons (<.

!(999) 999-9999;0;_
irst Specifies the input mask itself; for example, !(999) 999-9999

Second Specifies whether Microsoft Access stores the literal display characters in the table when you enter data. If you use 0 for this section, all literal display characters (for example, the parentheses and dashes in a phone number input mask) are stored with the value; if you enter 1 or leave this section blank, only characters typed into the control are stored.

Note: my personal preference is to STORE literal symbols for text fields, such as parenthesis or dashes on phone numbers --> use 0 (zero)
Third Specifies the character that Microsoft Access displays for the space where you should type a character in the input mask. For this section, you can use any character; to display an empty string, use a space enclosed in quotation marks (" ").
Note: I use "_"

something like this:
now the caveat: this will only happen to NEW phone numbers or to phone numbers that are edited ... so if you want the existing phone numbers to change, you have to edit them. If this was a sample table and you have one with a whole lot more records, let me know ... I have a program somewhere to loop through a recordset and do that -- but it will take time to find it and to explain how to use it (you would have to create another field)

read this thread:
Proper Data Type for Contacts -- changing InputMask
TY Crystal. CCSlice is a nickname. It is long funny story, but it stands for a mishap in a kitchen while making chicken cattitore. The database attached is a sample. The current db I am working with is over 2000 phone numbers and for confidentiality I did not post them. I would appreciate having a look at your program that would loop through the recordset.
Do you think adding additional controls to the form would help in updating the listbox that is bound to tblPhones?
et me know your thoughts.
Thanks Larry.
Hi CCSlice (you still didn't tell me your name <smile>)

There is some code for you:

Sub AddSymbolsToPhone()
   Dim r As DAO.Recordset
   Set r = CurrentDb.OpenRecordset("tblPhoneNumbers")
   Do While Not r.EOF
      If Not IsNull(r!PhoneNumber) Then
         r!PhoneNumber = Format(r!PhoneNumber, "999-999-9999")
      End If
   Set r = Nothing
   MsgBox "Done"
End Sub
Excellent. Thank you Crystal! My name is....
you're welcome, Colin wink.gif happy to help
Hi Colin,

BIG oops!

hope you have not run the routine to correct phone numbers on your database yet ... or, if you have, hope you have a backup. The "9" in the format mask should be a 0...

r!PhoneNumber= Format(r!PhoneNumber, "(000) 000-0000")

Edited by: strive4peace2008 on Wed Feb 27 15:07:18 EST 2008.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.