Full Version: How to open with a blank form
UtterAccess Forums > Microsoft® Access > Access Forms
RSIITGUY
I have a form based on a table. I have a combo box at the top of the form that finds a record in the table. Whenever I open the form the first record in the table always displays. Is there an easy way for the fields to all be blank until a record is selected in the combo box?
Thanks!
Doyce
Doug Steele
I'm assuming that the combo box is unbound. What I often do in that situation is set the form's RecordSource to nothing, and set it to the correct query in the combo box's AfterUpdate event.
On other words, create the form as normal, but go into the properties and remove whatever's in the form's RecordSource property. Then, add code like:
CODE
Private Sub cboFindRecord_AfterUpdate()
Dim strSQL As String
  
  strSQL = "SELECT Field1, Field2, Field3 " & _
    "FROM MyTable " & _
    "WHERE Field4 = " & Me.cboFindRecord
  Me.RecordSource = strSQL
  
End Sub
RSIITGUY
What am I missing here. I'm getting an error that says, "Compile error: Sub or Function not defined." Then it highlights Me.cboFindRecord in the "Where" line.
Here is my code:
Private Sub cboFindRecord_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Primary.[ClaimID], Primary.[Claim Date], Customers.[Cust Name], Primary.ContactName, Primary.ContactPhone, Primary.ContactEmail, qryUnionVinmaster.[Unit #], Primary.[Serial Number], qryUnionVinmaster.BOM AS [Model #], Primary.Complaint, Primary.Resolved, Primary.[Cust ID], Primary.LaborHours, Primary.LaborRate, Primary.InspectionNotes" & _
"FROM qryUnionVinmaster RIGHT JOIN (Customers INNER JOIN [Primary] ON Customers.[Cust ID] = Primary.[Cust ID]) ON qryUnionVinmaster.[Serial Number] = Primary.[Serial Number] &_"
Where "[ClaimID] = " & Me.cboFindRecord

Me.RecordSource = strSQL
End Sub
Doug Steele
cboFindRecord is what I chose to call the combo box. Replace it with the actual name of your combo box. (Note that that will mean that the sub will be named something else as well)
ote that the quotes on the last line of your SQL statement need to be in front of the word Where, not after it.
HAs well, if ClaimID is a text field, you'll need quotes around the value being passed:
CODE
  "Where [ClaimID] = '" & Me.cboFindRecord & "'"
RSIITGUY
I'm getting a Syntax error on From clause: Me.recordsource = strSQL
Doug Steele
Just noticed that your quotes are incorrect:
trSQL = "SELECT Primary.[ClaimID], Primary.[Claim Date], Customers.[Cust Name], Primary.ContactName, Primary.ContactPhone, Primary.ContactEmail, qryUnionVinmaster.[Unit #], Primary.[Serial Number], qryUnionVinmaster.BOM AS [Model #], Primary.Complaint, Primary.Resolved, Primary.[Cust ID], Primary.LaborHours, Primary.LaborRate, Primary.InspectionNotes" & _
"FROM qryUnionVinmaster RIGHT JOIN (Customers INNER JOIN [Primary] ON Customers.[Cust ID] = Primary.[Cust ID]) ON qryUnionVinmaster.[Serial Number] = Primary.[Serial Number] " & _
"Where [ClaimID] = " & Me.cboFindRecord
Me.RecordSource = strSQL
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.