B
Brandie
OK...the problem is with both the subject and the technology. How do I fix it?
Sprinks said:Brandie,
There is nothing wrong with the syntax of your statement, it is
grammatically correct. The only thing preventing you from resolving the type
mismatch is to determine the data type of the left and right hand sides of
all three equivalencies.
To narrow it down quickly, try it with a single field as the criteria, and
try the next one until you generate the error. Don't erase the rest of the
code, but move it to a new line and "comment it out" by placing a single
quote at the beginning of the line.
stLinkCriteria = "[BAA/RFP/RFI Number]=" & Me![BAA/RFP/RFI Number]
' Commented out code
' & " AND [Subject]=" & Me![Subject] & "" And [Candidate Technology] = " &
' Me![Candidate Technology]"
Good luck.
Sprinks
Brandie said:Private Sub Command93_Click()
On Error GoTo Err_Command93_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmDARPAeval"
MsgBox Me![BAA/RFP/RFI Number] & “ And “ & Me![Subject] & “ And “ &
Me![Candidate Technology]
stLinkCriteria = "[BAA/RFP/RFI Number]=" & Me![BAA/RFP/RFI Number] & "
AND [Subject]=" & Me![Subject] & "" And [Candidate Technology] = " &
Me![Candidate Technology]"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command93_Click:
Exit Sub
Err_Command93_Click:
MsgBox Err.Description
Resume Exit_Command93_Click
End Sub
Sprinks said:Sorry you're having trouble, Brandie. Access can be somewhat frustrating in
the beginning.
Please cut and paste your entire command button event procedure, and I'll
see if I can help.
Sprinks
:
I tried it exactly how you have it here and inside of parentheses and
quotatioins, the fist showed nothing but the original error and the second
just shows this:
MsgBox Me![Control1] & “ “ & Me![Control2] & “ “ & Me![Control3]
I'm so confused......
:
Brandie,
First, you are making a common error in misunderstanding fields and controls.
Fields are *table* objects that have a datatype. Fields don’t exist on a
form—controls do. Controls may be unbound, be a calculation, or be bound to
a field in the form’s underlying RecordSource. The controls themselves do
not have a data type, they are merely a “container†to display the
ControlSource.
This is important because the Openform method statement uses an SQL WHERE
clause, which is defined in the stLinkCriteria assignment statement.
stLinkCriteria = "[Field1]=" & Me![Control1] & " AND [Field2]=" &
Me![Control2] & " AND [Field3]=" & Me![Control3]
It is matching a *field* in the RecordSource of the form to be opened with
the value of a *control* in your subform. This is why I asked for the
fieldtypes (as shown in Table Design view) of the fields in the RecordSource
of the 2nd form. Your type mismatch error must be caused by at least one of
three expressions having differing datatypes on either side of the expression.
Use a MsgBox statement at the top of the command button code to display the
values of the three controls. Be careful to use the name of the control, not
the bound field; these may be different:
MsgBox Me![Control1] & “ “ & Me![Control2] & “ “ & Me![Control3]
Open the 2nd form’s RecordSource, and check these values against the
datatypes of the corresponding fields.
Hope that helps.
Sprinks
:
Brandie,
OK, got it now.
Please post:
- the name of your subform
- the ControlSource of each of the 3 subform controls you want to use as
matching criteria
- the Bound Column & the RowSource properties of any combo boxes on your
subform
- the field types of each of the corresponding fields in the RecordSource of
the second form
- the location of the command button that opens the second form. Is it on
the main form or the subform?
Sprinks
:
Misunderstanding.....the main form has one subform with a command button that
opens another form (it isn't a second subform). I think I got it if you can
tell me how to get rid of the type mismatch error, but I'm not sure.
:
Brandie,
I think the easiest way to implement this, requiring no code, is to place
your second subform as a subform on your first, rather than on the main form.
Link the two on all three fields, and they should always display the related
information.
"Type mismatch" occurs when expressions on opposite sides of an equal sign
are of different types. As I posted, this is probably due to trying to
equate the value of a combo box (which may *display* text, but actually *be*
a number) to an actual text value. Try inserting Msgbox statements or
writing the values to the Immediate Window with Debug.Print, for example:
MsgBox "MyControl = " & Me!MyControl
Hope that helps.
Sprinks
:
......The first form has a subform linked by the BAA/RFP/RFI Number. The
second form should be opening the record with the same info in the three
fields that appear on the subform. I almost got the code to work but I keep
getting an error that says "type Mismatch" What now?
:
Hi, Brandie.
These subforms--are they linked to the main form, that is, have you set the
LinkMasterFields and LinkChildFields properties of each subform? If they
were linked by the three fields you mention, you wouldn't need *any* code,
they would simply display the appropriate records.
Hope that helps.
Sprinks
:
I figured out why it changed back and fixed it...but now it doesn't work at
all. I think it has something to do with the fact that subject and
technology are on a subform on the first form. The way that I had before
worked it just tried to create a new record instead of saving the info on the
same one. and it showed the autonumber ID of the subject and technology
instead of the word. Do i have to do something different in the filter if
the info is on a subform?
:
Hi, Brandie.
This is an example of why most developers prefer a simple AutoNumber primary
key rather than one made of multiple fields. It makes life easier.
In any case, use the wizard and then modify the stLinkCriteria assignment to
include all your criteria:
stLinkCriteria = "[Field1]=" & Me![Control1] & " AND [Field2]=" &
Me![Control2] & " AND [Field3]=" & Me![Control3]
Hope that helps.
Sprinks
:
The first form has a record and in that record there are look ups to the
subject table and the technology table. These and the Project Number make up
the primary key for the main table(all three). The second form runs off a
query that runs off of the main table. When I enter information into the
first form and click on the comand button, it saves the form and opens the
second. I need the second form to display the first three fields acording to
the record shown on the first form(i.e.it
needs to be the same record). How do I do this?
I tried, in the second form, setting the default value property of the 3
controls to:
=Forms!NameOfFirstForm!ControlNameOnFirstForm
This works, but when saving the second form, it trys to create a new record
instead of it being the same record as the one on the first form. Please
help, this is driving me crazy. And please don't say use the wizard...I've
tried that and it only uses one control to link forms, not three.