Replies

I think that will work! Thanks a bunch Hans!

Well, I’ve almost got it working…. After moving the fields around as Hans suggested, i get the sums to total, but they are not quite right. Since the square footage is tied to the clusters, in which are made up of several rooms (without individual square footages), the query displays the cluster square footage for each room. This means the report is trying to sum all the instances of the square footages. If there is only one room in the cluster, then the sums work correctly. If there are three rooms in a cluster, and the cluster area is 400 s.f., then the sum is 1200 (3 x 400). It is summing the area for each room, not the overall area of each cluster. What can i do to fix this? I only have 2 hours to get this completed. Thanks so much for your help!

It’s been a while since i was heavy into Access.. assume i will make those changes and see if the report totals will work. Thanks for the tip!

here is the attachment… I guess i should have tried zipping it. After 12 hours of work, sometimes your brain just gets fried! I have included the two reports that I need done. There area labels indicating where and what sums need to be calculated. Thank you for everyone who is trying to help me out!

I’m coming up with numbers like 10 million! not quite right… In my tables, i have one with department clusters that each have an area square footage. I combine these in a query so each room shows up in it’s appropriate cluster. In the query, the area of the cluster displays for each room. I think this is what the total area is adding. Am i doing something wrong? thanks for your help!

Hans, You are just the greatest!!

worked like a charm!

Thanks Hans

Worked like a charm!!

Well, it looks like the problem lies in that i had the modal property of the main menu set to yes… I turned it off and things worked again.. Does anybody know why this would happen?

Thanks

So, is the program still comparing the original value to the values in the list? I did what you said, and even though the new value that displays in the combo box is also in the list, it still gives me the notinlist error. It would seem to me that if the new value was in the list, it should not give me the error, but if it is still comparing the old value, i don’t know what there is that i can do to stop it Should i consider giving up at this point? My last day working here is next friday, and this project needs to be done before i leave. I was hoping this was a simple problem (simple for the experts like you, anyways), but it is turning out to be a big hassle…

So, i guess if you have any other suggestions, i would be happy to try them, otherwise i will put this issue in the graveyard of things that can’t be done

Charlotte

adding Me.cmbCustomer.Undo before the value is changed appears to do nothing… is this what you meant, or am i doing something wrong here? I know you are very busy, and i appreciate the help you are giving me. I am on the verge of just telling the owners that we can’t check for abbreviations.. I would really not like to do that.

here is the code i am working with (including the undo you suggested).

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

On Error GoTo Err_cmbCustomer_NotInList

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

strNewData = NewData
strNewData = ChangeAbbreviations(strNewData)

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & strNewData & “‘ does not exist.” & vbCr & vbCr
Msg = Msg & “Do you wish 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)

‘ Create a new record.
Rs.AddNew
Rs![Customer Name] = strNewData
Rs![Customer Order] = “999”
Rs![Report Customer Name] = strNewData

‘ Save the record.
Rs.Update

‘ Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
Me.cmbCustomer.Undo
Me.cmbCustomer = strNewData
Me.cmbCustomer.Text = strNewData

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

Charlotte,

I tried adding it directly after the value changed, and it kept asking me to add the new value (basically calling the notinlist function again). Everytime i said OK, it asked me again. When i said No, it went to my custom message to select a customer from the list.

Any thoughts about johnhutchinson’s post? I am wondering if LostFocus would cause any unforseeable problems in the future. I will give it a whirl and see what happens

Thanks for your help, as always

Charlotte,
Thanks for your reply. You were right, this did get me closer. Now what happens is that everything gets added correctly and the combo box text changes to match the new value without triggering the notinlist event. However, when the program continues through until the end (exit sub), it says the item is not in the list (even though the item did actually get put in the list). Any idea what is happening?

Thanks for your help and patience.

Here is the notinlist event which i am using. The ChangeAbbreviations function returns an abbreviated version of the NewData string that is input. The part where i am changing the NewData to the new string is where i am having problems. Like i said, everything works (as far as adding the correct record, updating the combo box, etc), except where the text in the combo box (before the change) is not the same as the NewData. If you need more, i can post a dummy db with the sample form i created..

Thanks in advance

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

On Error GoTo Err_cmbCustomer_NotInList

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

NewData = ChangeAbbreviations(NewData)

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & NewData & “‘ does not exist.” & vbCr & vbCr
Msg = Msg & “Do you wish 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)

‘ Create a new record.
Rs.AddNew
Rs![Customer Name] = NewData
Rs![Customer Order] = “999”
Rs![Report Customer Name] = NewData

‘ Save the record.
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

Charlotte,
I tried what you said about passing the newdata to the ChangeAbbreviations function, and things worked very well – the value changed, the correct value was entered in the table, etc.. However, in the end after the record was added, the Text portion of the combo box was still the original value (before the abbreviations were added). I have tried everything i could think of to change that to the NewData variable, but no matter how i do it, it still tries to call the notinlist event again. If i don’t change the text, then the old value (withouth the abbreviations) is not in the list (which is expected because the abbreviated version is already there). What can i do from this point to iron this situation out? I can post my final code/form if you’d like…

Thanks for the tip, Charlotte
I will try what you suggested, but i have one question… What exactly is the difference between a subroutine and a function? If i know what the difference is, i will be able to use the appropriate one in the future when something similar occurs.

Thanks for your help and patience