Can't Figure out the problem in my code when Opening a form

[SD23]

I have two forms. They are both linked to different tables. In addition form 1 corresponds to form 2 through an ID value (text box). If the user inputs a ID value that already exists in form 1, I want the corresponding form 2 to open. Basically I want to open a specific form. I have written the code below. When this code is implemented, it opens all 500 forms no matter what I input in the ID value. I have found many similar posts but I am still not able to debug my problem. I am pressed on time and would appreciate any suggestions. Thanks.

Private Sub dnum_Exit(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String


Num = DLookup("[dnum]", "All Data Table", "[dnum]=[All Data Table].[Docket No]")
Suf = DLookup("[dsuf]", "All Data Table", "[dsuf]=[All Data Table].[Docket No Suffix]")
Pre = DLookup("[dpre]", "All Data Table", "[dpre]=[All Data Table].[Docket No Prefix]")


If Not IsNull(Suf) Then
Beep
MsgBox "The Docket Suf Already exists", vbOKOnly, "Duplicate Value"



stDocName = "Dockets Data Input Form"
stLinkCriteria = "[Docket No Suffix]=" & "'" & Me![dsuf] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


End If
End Sub

 

[Matt Greatorex]

Suf, Num and Pre haven't been declared, so you can't set Suf to be anything at all, null or otherwise. That would mean the code never hits the 'beep' stage.

Have you tried displaying a message box for each, just after they are supposed to change value? If it displays as blank, that's your problem.

 

[SD23]

I displayed a message box after each and the message box does in fact appear and the same problem happens (all forms open). In addition, I think the code does go passed beep, because the message box "The docket number already exists" appears before the next form opens. I also tried declaring the variables, but i got the same problem which is that all the second forms open.

 

[SD23]

so i figured part of it out. I added

DoCmd.GoToRecord , , , stLinkCriteria

to the bottom under the DoCmd.OpenForm...

Now it shows the correct form I want (it has the similar ID value), but I get an error which I am not sure how to debug. The error is Run-time 2498: "An expression you entered is the wrong data type for one of the arguments. "

Any suggestions on how to fix this.

 

[Matt Greatorex]

I'd look at the link criteria, as that seems to be the most likely place for an error. Instead of

stLinkCriteria = "[Docket No Suffix]=" & "'" & Me![dsuf] & "'"

try

stLinkCriteria = "[Docket No Suffix]='" & Suf & "'"

and maybe declaring the variables, if that doesn't fix it?

 

[SD23]

I tried

stLinkCriteria = "[Docket No Suffix]='" & Suf & "'"

but that didnt seem to work. I still got the same error. I also tried declaring the variables, but I still got the same error. Im not sure what else to check.

 

[RuralGuy]

If [Docket No Suffix] is numeric then drop the quotes.
stLinkCriteria = "[Docket No Suffix]=" & Me![dsuf]

 

[SD23]

The docket no suffix is not numeric. It is a text field.

 

[SD23]

Does it matter is dsuf is a pull down box?

 

[RuralGuy]

Yes. By referencing the control as you have you are pulling the default property of the ComboBox. You can pick the Column you wish explicitly with the syntax Me.ComboBoxName.Column where n is a zero based index making the second column Me.ComboBoxName.Column(1) as an example. Using your ComboBox names of course. Look up Value in VBA help for an explanation of the default value.

 

[SD23]

i tried it with the Me.combobox.column but I still got the same error(wrong data type in the expression of one of the arguments). I really have no idea how to debug this. It is very frustrating.

 

[RuralGuy]

Does it matter is dsuf is a pull down box?

Does this mean you are using LookUp fields? The Evils of Lookup Fields in Tables

 

[SD23]

I was using lookup fields, because that was the only way I could think of achieving my goal. The code I have finds what im looking for but has an error first. What are my other options?

Basically I have a ID Value on form1 that the user inputs. This ID value (Suf) is stored in a table. The ID value on form1 also corresponds to a certain form 2. Form 2 also has its own table. I wanted to create code that looked through the table of form 2 to make sure the inputted ID in form1 is not a duplicate. If it is a duplicate, then it should display a message and open the duplicate form 2 (if duplicate open the original form 2 that was created before). Is there a way to do this and if so how would I go about doing?

Rural guy i really appreciate your help. thanks for taking the time to help me. If you can let me know how to do this, i would be very greatful. Thank you.

 

[RuralGuy]

I'm not sure I follow what you are trying to accomplish. At the very least change *all* of your lookup fields to TextBoxes. You will now be able to see what is actually in the table when you look at it. It is ok to use ComboBoxes on *forms* to "LookUp" values to store in a table but don't put the lookup in the table. Why would the table for Form1 and the table for Form2 have conflicting data? I suspect you have a normalization problem that once resolved would make this particular issue simply go away. With more details on your tables someone here would probably be able to offer guidance on how to revise the fields or restructure your tables.

 

[SD23]

im sorry I misunderstood what you said. My tables do not have look up fields. So now imnot sure what to do. I thought you meant that you could not use dlookup with opening forms. I dont have conflicting data in form 1 and form 2. It is that right now two different forms can have the same ID value because the user can input the ID. I want to prevent this from happening. If the user inputs an existing ID value in form1, I want the existing form 2 with the same ID value to open.

 

[RuralGuy]

I guess I'm lost because Forms do *not* have values. Tables, queries, controls (bound or unbound) can have values. I don't know what a form with a value would look like. Maybe a sample of your db to demo the issue.

Edit: I'm sorry but so far I don't think we are using the same language. Sometimes I can guess properly as to what is being described but not in this case.

 

[SD23]

sorry for the confusion. I guess I am using the word value loosely.

Basically, I have two forms (Form 1 and Form 2). Each are linked to different tables (table 1 and table 2). When the user clicks submit on form 1, it goes to form 2.

Now on form 1, there is a text box so the user can input an ID. After he fininishes filling out form 1, the user clicks submit and is taken to form 2, where the user can fill out more information. The two forms have the same ID. For example if the user inputed SJA as the ID, Form 1 and Form 2's ID would be SJA.

Currently, the user can input existing IDs in form 1 and it creates a new form 2 with the same existing ID. This creates duplicate IDs which can get confusing. For example

USER1 input ID=SHA so the ID for form 1 and Form 2 is SHA
USER2 input ID=SHA so the ID for Form 1 and Form 2 is SHA

What I want:
I want to create code so that if the user inputs an existing ID, the code will instantly recognize it, display a message, and open the corresponding form 2. Since the ID was a duplicate, the form 2 was already created before. In the above example, when user2 inputed the existing ID SHA, the code would pull up the form 2 of USER1.

I hope this clarifies what I am looking for and you are able to give me some suggestions. I am pretty new at this as Im sure you noticed. Sorry for the inconvenience.

on a basic level something like this:

If form1.ID is existing
msgbox "Existing"
Open ID.form2 of that existing ID
End If

If Form1.ID is new
msgbox "new ID"
open new form
End IF

 

[Banana]

Just asking-

Is Table2 bounded to Form2 supposed to have *only* one entry from the Table1?

If that is the case, it's a one-one relationship, and you may want to change that accordingly...

If you do want to keep one-many relationship but locate the existing ID, you need to pass the argument with the ID.

So in a control where ID is filled in, or in the button where user is taken to form 2, you use AfterUpdate Event-

**Air code**

If Not IsNull(Dlookup("ID", "Table1", "ID = " & MyControlName & ")) Then
   'I cannot remember how many commas you need; Me.ID is supposed to go where OpenArgument class is
   Docmd.OpenForm "Form2", , , , Me.ID
Else
   Docmd.OpenForm "Form2", , , acAddNew
End If

and in Form2's Open event,

If Not IsNull(Me.OpenArgs) Then
   'Move record to the ID of the OpenArgs
   'Search the forums on how to move a record
End If

 

[SD23]

Table 2 which is bound to form2 has a few entries from table 1 such as name date etc. I will try the code out at work tomorrow.

 

[Banana]

Wait, wait.

If the table2 share same fields as table1, then you do have normalization problem. Data should never be repeated.