Opening a form from a command button

Question

I  have a form with a command button and a combox control which shows a list

of dates. The user chooses a date and then clicks a command button. The

combobox listing the dates is "cmbmonth".

When the command button is clicked it should open a form "frmhighvaluedeals"

and show the records for that date.

The form is a main form based on a query "qryhvcomp" and has a tab control

with two pages. On page 1 is a form based on a query "qryhvdealspt1" and on

page 2 is a form based on a query "qryhvanalysis"

The main form and the subforms are linked on the Primary key of the table

tblhvcomp (on which the query qryhvcomp is based) and the foreign key on

tables tblhvdealspart1 and tblhvanalysis on which the other two queries are

based.

The code on the OnClick event of the command button is:

Private Sub cmdopenrecord_Click()

On Error GoTo Err_cmdopenrecord_Click

  Dim strtxtdate As String

  strtxtdate = Format(Me.cmbmonth.Value, "mmmm/yyyy")

  Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"

  Forms!frmMain!SubForm1.Form.RecordSource = _

  "SELECT * FROM [qryhvcomp] " & _

  "WHERE [txtmonth] = #" & strtxtdate & "#"

Exit_cmdopenrecord_Click:

   Exit Sub

Err_cmdopenrecord_Click:

   MsgBox Err.Description

   Resume Exit_cmdopenrecord_Click

End Sub

The field "txtmonth" is a date field in the table tblhccomp. So I want to

show all records where the value of cmbmonth is equal to txtmonth in table

tblhvcomp and populate the linked records on the two subforms.

When I click on the command button, the form opens but with no data showing

and the record navigation box shows 1of1 and the control for the field ID

(which is the primary key) on the main form says (NEW) as if the form has

opened for input. All the Data Entry properties of all the forms are set to

"No" and the Recordset Type is set to Dynaset (Inconsistent Updates). When I

close the form I get a brief flash of data in all the controls before the

form closes.

Can anyone help with this problem?

Many thanks

Tony

Answer 1

Also check if your queries are returning results. A common reason for a blank form is a query that returns 0 records.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 2

Hi TonyWilliams,

To try and narrow down the cause of the issue, you may want to start by simplifying the process a bit. Perhaps you could try just limiting it to showing data on one form first. In addition to this, you could try hard coding the date values in your code. Once you get this working, then continue to add in the other steps.

Best Regards,

Nathan Ost

Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.