Replies

Ive tried it as based on a query and typed into its row scource.

My appologies for not being clear. Consider a form intended to look up envelope numbers for a church congregation:
field1 = [memberid]
field2= [envelopenumber]
These two fields are in a combo box intended to find the memberid when the user types in the envelope number. The problem is that the combo box doesn’t work when the envelope number is a numeric field.

Sorry for the late reply. Been out of town. All computers are listed as Workgroup (changed the XP default). Do you mean the properties page for the NIC in the device manager? If so I can find no “master browser” settings.

Firewalls on all computers is turned off including the one in XP provided it is only to be unchecked in the network properties.

Small perhaps, but more than the 91 that Northwind has. Is it this size that could be causing the continual crashes in my database while not in the Northwind? I am curious as to what code others would have written in place of:

Private Sub Form_Current()
Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![subform2].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub

For Charlotte: If you use the form wizard to construct a form with two subforms, such as the “Customers Orders” form in the Northwind database, the wizard will insert a “Current” event in the first subform which requeries the second subform. I apologize for not being able to put up a sample, but if you look at the Northwind database (courtesy of Microsoft), its structure is identical. Except that my database has a couple thousand records. Could this influence the query?

The setup and form are indeed identical to the “Customers orders” in the Northwind database. There too, you will notice the “On current” event, but it doesn’t crash. When I removed the same event from my form, and placed it in the “on click event”, no more crashes. But now the user would have to click the “select record”. And I’m curious WHY!!? The only difference that I can see is that I have four combo boxes setup as searches (find by last name, find by phone, etc). Could this cause query problems?

I tried to attach a stripped down version but is 112 Kb at max compression. Will try importing everything into new database and if still a problem post sql.

Both subforms are on the main form (as in Northwind). Subform1 is linked to the main form, subform2 is linked to subform1.
Subform1: Child=CustomerID, Master=CustomerID
Subform2:Child=OrderID, Master=[Ordersubform].form!orderid

This current event is being fired from the subform (subform1). I’m trying to requery the subsubform(subform2). Imagine the typical example of a customer (table) with several orders (subform1) and the items in each order(subform2). Without the query, I can click an order but the items do not change. Its basically the same code as in the Northwinds database example provided by Microsoft as samples. But I can see what you mean about it being in the on current event causing problems. If this is the case, I think Microsoft goofed because the wizard places it there too. How can I link the two subforms without a requery code and have the order details refreshed whenever I click a different order? or what code would you use?

I too have used this form setup in other databases without a problem. I’m quite confident the links are fine (relationships and syntax). Remember, I did build the form at the birth of the application and it worked fine. I have partially solved this riddle but first I will explain further I could not replace subforms on this form nor build a NEW form with subforms either manually or with a wizard. The wizard would go through the whole process but refuse to build any part of the form after clicking the “Finish” button. If I start a brand new database, I CAN construct the form, subform and subsubform, UNLESS I import the tables (even without data). Then I get wizard error again, etc. The only things I have done that I can think of is add a couple of modules and add data. Removing the following module seems to help. But why I don’t know. Can you see anything in it that would cause this instability? I am now able to reconstruct the form but it will still crash on certain customers.

Public Function RemoveCharacter(strRemovalCharacter As String, strTableName As String, strFieldName As String)
‘===============================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 27/10/2001 ‘
‘ Purpose: To remove any characters from a field in a table ‘
‘===============================================================’
Dim rstPerson As Recordset
Dim intHyphenPos As Integer
Dim strFieldValue As String

Set rstPerson = CurrentDb.OpenRecordset(strTableName, dbOpenTable)
With rstPerson
Do Until .EOF
Do
strFieldValue = Nz(.Fields(strFieldName))
intHyphenPos = InStr(1, strFieldValue, strRemovalCharacter)
If intHyphenPos > 0 Then
strFieldValue = Left(strFieldValue, intHyphenPos – 1) & _
Right(strFieldValue, Len(strFieldValue) – intHyphenPos)
End If
If strFieldValue .Fields(strFieldName) Then
.Edit
.Fields(strFieldName) = strFieldValue
.Update
End If
Loop Until intHyphenPos = 0
.MoveNext
Loop
.Close
End With

Set rstPerson = Nothing
End Function

I have checked all tables for null values in linked fields. None. I deleted the subsubform (orderdetails). The form worked. I tried to add a new subform but despite linking the fields and double checking, could not get it to work. Tried to reconstruct the form from scratch with the wizard but says it cannot construct form. I tried creating all new form with new tables (no data) and it will not do. i.e. one subform it will do, two it will not. I have run compact and repair. no change. Any ideas?

I see … (“said the blind man”). Thanks Jayden!

Downloaded fine this time. To work we go.

I don’t usually have a problem downloading from this site but this attachment is locking me out. Doesn’t even ask for my password.

1. Thanks. That works.
2. I tried to use an input mask in the properties of a combo box, set to look up customerID values (column1) when the user enters a phone number (column2). The phone numbers are otherwise being displayed without the hyphen despite trying to set the format property in the form or the table. They do display fine in the table where an input mask is set. So the I guess the question is how do I format the phone numbers so they will appear correctly. Otherwise my combo is set up the way you describe.