Form/Subform SQL problem

[MisterMan]

Hi all,

I have a form and a subform, let us call the form attempt1 and the subform attempt2 (Witty, huh?).

Attempt1 is run off a query that allows the user to just type a Unique ID code and pulls up a record that is displayed in the form. This works wonderfully, and upon finishing it, I patted myself on the back.

My problem is this. I want the subform to pull up information from another table based upon the information displayed in 4 boxes in attempt1. I have tried to do this with a sql statement in the open event of attempt2, but it simply asks for the information to be inputted by hand.

Any suggestions?

 

[traingamer]

You need to have the Master:Child relationship set up properly between the forms.

(You're not a chemistry teacher, are you?)



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MisterMan]

Nope, martial arts instructor actually (Well, and budget examiner)

Mind running me through setting it up correctly?

 

[MisterMan]

If it helps matters at all, this is the VBA/SQL code I was trying to use.

Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT tblSigned.[School Year Start], tblSigned.[School Year End], tblSigned.CertNumber, tblSigned.CertType, tblSigned.Reason " & _
"FROM tblSigned " & _
"WHERE (((tblSigned.[School Year Start])= " & Me!SYS.Value & ") AND ((tblSigned.[School Year End])= " & Me!SYE.Value & ") AND ((tblSigned.CertNumber)= " & Me!CN.Value & ") AND ((tblSigned.CertType)= '" & Me!CN.Value & "')); "

End Sub


Please note that I used the macro setvalue command to make 4 text boxes in which the information from attempt1 was put into. I thought this would make it easier.

 

[Ascentient]

I just did a similar form doing similar to what you are wanting to do.

I have a form that looks up Clients based on the search criteria enabled. When one is enabled it sets the links between two of my subforms.

My Main Subform is linked to my cboboxes which pulls all quotes for that particular client. My secondary subform lists the detail of the quote from the Main Subform.

Here is some code I used to switch the links when my cboboxes where used.

Private Sub cboCustomerID_AfterUpdate()
Me.subQuoteHeader.LinkMasterFields = "cboCustomerID"
If Me.subQuoteHeader.Form.RecordsetClone.RecordCount > 0 Then
txtBoxVisible
cmdPrintQuote.Enabled = True
Else
MsgBox "No Quotes Found for this Customer. Please select another customer."
cboCustomerID.SetFocus
cboCustomerID = ""
End If
End Sub

Private Sub cboCustomerName_AfterUpdate()
Me.subQuoteHeader.LinkMasterFields = "cboCustomerName"
If Me.subQuoteHeader.Form.RecordsetClone.RecordCount > 0 Then
txtBoxVisible
cmdPrintQuote.Enabled = True
Else
MsgBox "No Quotes Found for this Customer. Please select another customer."
cboCustomerName = ""
cboCustomerName.SetFocus
End If

End Sub

Here are the properties of my two subforms:
-Main form-
link Child Fields: CustomerID
Link Master Fields: cboCustomerID 'Base on Code above.
Form Detail itself:
RecordS Source: SELECT tblQuoteHeader.* FROM tblQuoteHeader;

Secondary Form (Quote Detail)
link Child Fields: QuoteNumber
Link Master Fields: [subquoteheader].Form![QuoteNumber]
-Form Detail itself-
RecordS Source: SELECT tblQuoteDetail.QuoteNumber, tblQuoteDetail.TotalTons, tblQuoteDetail.Material, tblQuoteDetail.Description, tblQuoteDetail.PricePerTon, tblQuoteDetail.DelChargePerTon, tblQuoteDetail.PlantNumber, tblQuoteDetail.PlantName FROM tblQuoteDetail;

Basically this allows me to select a customer from 1 of 2 combo boxes. On the afterupdate event it populates the Main Subform with the Quote Header information. When a quote header is selected the Seconday form populates the quote detail.

 

[traingamer]

Remove the where clause from the query and put all the fields from the form in the Master:child Relationships (or just make sure the relationships are defined and have the wiazrds create the forms for you.)
To define them yourself, click on the outer border of the subform and update the 'Link Child Fields' and 'Link Master Fields' in the properties.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MisterMan]

The chidl/Master relationship thing worked like a charm to fill the boxes I needed filled. However, I still have a boggle.

I guess I didn't make it clear, but what I had hoped to do was use the criteria in those four boxes to return a record from a table and put it into attempt2. I tried writing SQL to return that into the box I wanted, but I kept getting the usual response one would get from just writing "Where Thing1 = [Enter Thing1].

Sorry for being so slow.

 

[Ascentient]

What is the current sql statement you have? I see what you are saying now, but need some additional detail on the fields, etc.

The record/control source of attempt2 can be updated based on the contents of the txtboxes and a requery.

 

[MisterMan]

I think I solved it... There was some code in the original SQL statement I didn't want and forgot to kill.

I am 99.9% sure it works now. Thanks! If not, I will be back to beg