Replies

what i have been doing is calling the function at the notinlist event (which triggered because the entry wasn’t in the list).. then it changes the value of the text in the combobox with the function. When it changes the text, this new text also is not in the list, so it triggers the notinlist event in the middle of the current event. It does do all this before the record is saved, but like i said, it triggers the event more than once.

Here is the code in my form…

Option Compare Database
Option Explicit

Dim SearchValue, ReplaceValue, SearchStr, Source, CustomerString As String

Private Sub cmbCustomer_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

CustomerString = cmbCustomer.Text

On Error GoTo Err_cmbCustomer_NotInList

SearchStr = CustomerString
Source = “cmbCustomer”

Call CheckForAbbreviations

‘ Exit this subroutine if the combo box was cleared.
If NewData = “” Then Exit Sub

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & NewData & “‘ is not in the list.” & vbCr & vbCr
Msg = Msg & “Do you want to add this Customer? @@Please make sure punctuation and spelling are correct!!”
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
‘ If the user chose not to add a customer, set the Response
‘ argument to suppress an error message and undo changes.
Response = acDataErrContinue
‘ Display a customized message.
MsgBox “Please select a Customer from the list”
Else
‘ If the user chose to add a new customer, open a recordset
‘ using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset(“tblCustomers”, dbOpenDynaset)

Rs.AddNew
Rs.Update

‘ Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_cmbCustomer_NotInList:
Exit Sub
Err_cmbCustomer_NotInList:
‘ An unexpected error occurred, display the normal error message.
MsgBox Err.Description
‘ Set the Response argument to suppress an error message and undo
‘ changes.
Response = acDataErrContinue

End Sub

Private Sub CheckForAbbreviations()

SearchValue = “company”
ReplaceValue = “Co.”

Call ChangeAbbreviations

SearchValue = “corporation”
ReplaceValue = “Corp.”

Call ChangeAbbreviations

SearchValue = ” and ”
ReplaceValue = ” & ”

Call ChangeAbbreviations

End Sub

Private Sub ChangeAbbreviations()
Dim searchstart, searchend As Integer

SearchStr = CustomerString

searchstart = InStr(1, SearchStr, SearchValue)

If searchstart 0 Then
searchend = searchstart + Len(SearchValue)

cmbCustomer.text = Left(SearchStr, (searchstart – 1)) & ReplaceValue & Right(SearchStr, (Len(SearchStr) – (searchend – 1)))

End If

End Sub

Hmm….

The reason i didn’t was because in the past, the auto-center property was not working correctly… However, since you said that, i tried it out. I found out that the auto-center does not work when you go from design view to form view (which was how i was testing the forms out). I just now tried it by opening the form from the database menu, and wouldn’t you know it.. It worked! I guess that goes to show you that if something does not work how you would expect it to, take the time to find out why… Thanks for pointing that out Hans.. i kind of feel like an idiot now!!

Mark and Charlotte:

I think i figured out an acceptable workaround… (and if this is something either of you hinted at but i didn’t catch, i apologize). The reason i wanted to change screen resolution was so my forms were centered and look good on the screen. I accomplished this by figuring the width and height of the screen (5900×3900 twips @ 800×600). Then i set the calculated center of the form to the calculated center of the screen…

What i have now decided to do was to simply check the computer’s resolution, then change the calculated center depending on that screen resolution. It seems like a simple solution to the problem, but this is really all i was looking for in the first place .

Thank you both for your help!!

Thanks Mark
I appreciate your continued help

Thanks Charlotte…

So i guess you are saying to design it for 800×600 and let the forms and such for 1024×768 resolution be off center? I had toyed with that and really that is how it is set up, but things just look alot better if everyone is at the same resolution.
Let me know if you meant something different. Thanks!

Thanks Mark,
I like the idea of resizing the app to cover the whole screen… however your attachment only resizes a form (unfortunately this will not work with the scheme of things i have set up.) The first link you gave me looks intriguing, however i cannot get the code to work. If there would be any way you could check the code out, that would be fantastic. I am not too adept at code dealing with api functions and such…

I also agree with you and charlotte about not changing settings, but i am unsure of any other way to go around the problem… If either of you two have any other ideas, i am absolutely open to suggestions )

thanks a bunch

Thanks for your reply
I had checked that article out previously, and it did hide and show the taskbar, however it does not actually set the “auto-hide” or “always-on-top” property. I will admit, as i explained my problem earlier, it would seem that this article would solve it.

I will attempt to explain the problem further… I am using a procedure that changes the resolution to 800×600. This is all fine and dandy except that after the resolution changes, the taskbar dissappears and the access window does not go all the way down to the bottom of the screen (it goes to the top of where the taskbar was). The only way that i have found to workaround the problem that is acceptable to me is when the “auto-hide” is set on or “always-on-top” is off. That way the access window extends all the way to the bottom of the screen. (the taskbar is still missing, but i am willing to sacrafice that). I want to be able to change either of those settings to be able to see the full screen, then change the setting back when the application closes.

Hopefully this clarified my problem. If you have any different suggestions, please let me know.

Thanks in advance

well, much of the data in some fields is only several words or so. however sometimes there are many words in the field, and i would like the users to be able to read all of what is in that field. perhaps i don’t need to be able to resize the field, but can the user get to all the data in a field that is not long enough? (perhaps selecting the field and pressing the “End” key)

thanks for your input

thanks guys, that seemed to fix the problem. i guess i misunderstood our IT guy – i thought everyone had the same rights. It turned out that the two secretaries had “Modify” rights, but not “Create” rights. things are working now.

i just wonder if there is a way to give all users all the rights, except prevent them from seeing the files in windows explorer? That way Access can do whatever it needs, but i don’t have to worry about them going in and deleting the database or something… is this even a possiblity?

thanks for your help

Smoke Eater:

I have had this problem in the past, though it had to do with employee names (sometimes they change their names, etc.)
You can use this code by creating two forms: one that is hidden and uses the table in which you want to update as its
recordsource. (i used frmUpdate in the example for this) The other is where you select the name you want to change
and type in the new name. (i used frmChangeName in the example for this). on frmUpdate, the only field you need
is the one you are updating (i used CustomerName in this example).

this may not be the best or most efficient way to do this, but it has worked for me. you can also search the customer table and simply change the customer from one to the other instead of creating a new customer.

Insert this code into an event on the frmChangeName form after you have selected the old name and provided the new name:

DoCmd.OpenForm “frmUpdate”, acNormal, , , , acHidden

Insert this code into the frmUpdate form.

Private Sub Form_Load()
Dim totalrecords As Integer

DoCmd.GoToRecord , , acLast
totalrecords = Me.CurrentRecord
DoCmd.GoToRecord , , acFirst ‘this bit of code finds out how many records need to be searched

If totalrecords > 0 Then
While Me.CurrentRecord <= totalrecords
Call changenames
If Me.CurrentRecord < totalrecords Then
DoCmd.GoToRecord , , acNext
Else
DoCmd.Close acForm, "frmUpdate", acSaveYes
Exit Sub
End If
Wend
End If

DoCmd.Close acForm, "frmUpdate", acSaveYes

End Sub

Private Sub changenames()

'this will check all records in the table you are searching. if the CustomerName
'matches the name you want to change, then it will change it to the new name. If
'it does not match, it will simply skip that record and move to the next one

Dim sourceform As String, sourcecontrol As String, ctl As Control
Dim sourcecontrol2 As String, ctl2 As Control
sourceform = "frmChangeName"
sourcecontrol = "txtOldName" 'the field with the name you want to change
sourcecontrol2 = "txtNewName" 'the field with the name you want to change the old one to

Set ctl = Forms(sourceform)(sourcecontrol)
Set ctl2 = Forms(sourceform)(sourcecontrol2)

If CustomerName = ctl.Value Then CustomerName = ctl2.Value

End Sub

Let me know if you have any problems with this. HTH

thanks guys,
i changed the startup to shared… i will post back if i have any more problems.

looks like that will work. Thank you everyone for all your help!

Thanks again

charlotte,
thanks for taking the time to do that… i just now checked it out and things were ok. however, the print options you supplied automatically print the document. we need to be able to change settings like quantity, etc. the “Print” option in the normal “File” menu does what we want, but the “Print” button in the other toolbars automatically prints. I tried adding the desired print option (actually i added the default “file” menu to your toolbar) and the option was available. if you could do me a favor and take a look at the db i am attaching. try adding a new menu (with the non-auto print option) and see if it gives you any problems. your help would be appreciated.

thanks

hi charlotte
i think i imported it from a different db i worked on… however, i just tried to create a new one and it did not help. i am going to try with the northwind database, but i am out of time at the moment. i will let you know what i find out

thanks for your continuing help

i have the stripped down file, but it is too big to attach. If anyone would like to have a try at it, let me know and i will email it to you.

Thanks