Opening Form

P

Paul3rd

I have a form w/1 unbound combo box(combo26)
and 13 text boxes. Currently when I open the form
all of the text boxes show values from record #1.
I would like the form to have empty text box fields
until the user makes a selection in the combo26 box.
I've started working on the code as follows:

Private Sub Form_Load()
Dim indx As Integer

With Me.Controls
For indx = 0 To .Count - 1
If Me.Combo26 = Null Then
If (TypeOf Me.Controls(indx) Is TextBox) Then
Me.Controls(indx).Text = Null
ElseIf Me.Combo26 = "*" Then
Me.Controls(indx).Text = Not Null
End If
End If
Next
End With


End Sub

The code compiles OK with no error but it does nothing.
Can someome help?
Thanks in advance,
 
J

Jeff Boyce

You can set a form's DataEntry property to Yes. This opens the form in data
entry mode (no records showing).

Or, you can set the form's source to be a query that looks to the combobox
for the recordID to display. In the AfterUpdate event of that combobox, use
something like:

Me.Requery

This causes the form's source to be requeried, whereupon the record matching
the one selected in the combobox is displayed!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Paul3rd

Thank you for your input:
When I set the "Data Entry" property of the form to "Yes"
The form does open with empty fields, however I get a code error when
I make a selection in the combo box because I have the following
code in the AfterUpdate property of that combo box:

Private Sub Combo26_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] =" & Me![Combo26]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I also tried to set the criteria in the forms' underlying query
(the ID field) to:

[Form]![combo26]
and I placed
Me.Requery
ahead of the Me.RecordsetClone line in the
AfterUpdate property of the combo box

When I tried to open the form I got a dialog box asking for
"Combo26 parameter".
That's why I was trying to use code to make the text boxes empty.
 
J

Jeff Boyce

Did you try the second approach I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paul3rd said:
Thank you for your input:
When I set the "Data Entry" property of the form to "Yes"
The form does open with empty fields, however I get a code error when
I make a selection in the combo box because I have the following
code in the AfterUpdate property of that combo box:

Private Sub Combo26_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] =" & Me![Combo26]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I also tried to set the criteria in the forms' underlying query
(the ID field) to:

[Form]![combo26]
and I placed
Me.Requery
ahead of the Me.RecordsetClone line in the
AfterUpdate property of the combo box

When I tried to open the form I got a dialog box asking for
"Combo26 parameter".
That's why I was trying to use code to make the text boxes empty.


Jeff Boyce said:
You can set a form's DataEntry property to Yes. This opens the form in
data
entry mode (no records showing).

Or, you can set the form's source to be a query that looks to the
combobox
for the recordID to display. In the AfterUpdate event of that combobox,
use
something like:

Me.Requery

This causes the form's source to be requeried, whereupon the record
matching
the one selected in the combobox is displayed!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
P

Paul3rd

Yes, I set the forms' record source property to a query and set the criteria
on the ID field to [Form]![Combo26].
The query for the form has many items but now ends with (SQL)

WHERE (((JOBS.ID)=[Form]![Combo26]));

I also placed Me.Requery ahead (as 1st line) in the AfterUpdate property
of the Combo26 box. So the AfterUpdate subprocedure now looks like this:

Private Sub Combo26_AfterUpdate()
Me.Requery
Me.RecordsetClone.FindFirst "[ID] =" & Me![Combo26]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

So now when I try to open the form I get the following dialog box:
"Enter Parameter Value"
for "Form!Combo26"
Thank You for your help,
Paul
Jeff Boyce said:
Did you try the second approach I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paul3rd said:
Thank you for your input:
When I set the "Data Entry" property of the form to "Yes"
The form does open with empty fields, however I get a code error when
I make a selection in the combo box because I have the following
code in the AfterUpdate property of that combo box:

Private Sub Combo26_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] =" & Me![Combo26]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I also tried to set the criteria in the forms' underlying query
(the ID field) to:

[Form]![combo26]
and I placed
Me.Requery
ahead of the Me.RecordsetClone line in the
AfterUpdate property of the combo box

When I tried to open the form I got a dialog box asking for
"Combo26 parameter".
That's why I was trying to use code to make the text boxes empty.


Jeff Boyce said:
You can set a form's DataEntry property to Yes. This opens the form in
data
entry mode (no records showing).

Or, you can set the form's source to be a query that looks to the
combobox
for the recordID to display. In the AfterUpdate event of that combobox,
use
something like:

Me.Requery

This causes the form's source to be requeried, whereupon the record
matching
the one selected in the combobox is displayed!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form w/1 unbound combo box(combo26)
and 13 text boxes. Currently when I open the form
all of the text boxes show values from record #1.
I would like the form to have empty text box fields
until the user makes a selection in the combo26 box.
I've started working on the code as follows:

Private Sub Form_Load()
Dim indx As Integer

With Me.Controls
For indx = 0 To .Count - 1
If Me.Combo26 = Null Then
If (TypeOf Me.Controls(indx) Is TextBox) Then
Me.Controls(indx).Text = Null
ElseIf Me.Combo26 = "*" Then
Me.Controls(indx).Text = Not Null
End If
End If
Next
End With


End Sub

The code compiles OK with no error but it does nothing.
Can someome help?
Thanks in advance,
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

See in-line below...

Paul3rd said:
Yes, I set the forms' record source property to a query and set the
criteria
on the ID field to [Form]![Combo26].
The query for the form has many items but now ends with (SQL)

WHERE (((JOBS.ID)=[Form]![Combo26]));
Click to expand...

This SQL does not look correct. I believe it needs to be:
WHERE (((JOBS.ID)=[Forms]![YourFormName]![Combo26]));

Regards

Jeff Boyce
Microsoft Office/Access MVP
I also placed Me.Requery ahead (as 1st line) in the AfterUpdate
property
of the Combo26 box. So the AfterUpdate subprocedure now looks like this:

Private Sub Combo26_AfterUpdate()
Me.Requery
Me.RecordsetClone.FindFirst "[ID] =" & Me![Combo26]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

So now when I try to open the form I get the following dialog box:
"Enter Parameter Value"
for "Form!Combo26"
Thank You for your help,
Paul
Jeff Boyce said:
Did you try the second approach I suggested?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Paul3rd said:
Thank you for your input:
When I set the "Data Entry" property of the form to "Yes"
The form does open with empty fields, however I get a code error when
I make a selection in the combo box because I have the following
code in the AfterUpdate property of that combo box:

Private Sub Combo26_AfterUpdate()
Me.RecordsetClone.FindFirst "[ID] =" & Me![Combo26]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I also tried to set the criteria in the forms' underlying query
(the ID field) to:

[Form]![combo26]
and I placed
Me.Requery
ahead of the Me.RecordsetClone line in the
AfterUpdate property of the combo box

When I tried to open the form I got a dialog box asking for
"Combo26 parameter".
That's why I was trying to use code to make the text boxes empty.


:

You can set a form's DataEntry property to Yes. This opens the form
in
data
entry mode (no records showing).

Or, you can set the form's source to be a query that looks to the
combobox
for the recordID to display. In the AfterUpdate event of that
combobox,
use
something like:

Me.Requery

This causes the form's source to be requeried, whereupon the record
matching
the one selected in the combobox is displayed!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form w/1 unbound combo box(combo26)
and 13 text boxes. Currently when I open the form
all of the text boxes show values from record #1.
I would like the form to have empty text box fields
until the user makes a selection in the combo26 box.
I've started working on the code as follows:

Private Sub Form_Load()
Dim indx As Integer

With Me.Controls
For indx = 0 To .Count - 1
If Me.Combo26 = Null Then
If (TypeOf Me.Controls(indx) Is TextBox) Then
Me.Controls(indx).Text = Null
ElseIf Me.Combo26 = "*" Then
Me.Controls(indx).Text = Not Null
End If
End If
Next
End With


End Sub

The code compiles OK with no error but it does nothing.
Can someome help?
Thanks in advance,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clearing combo boxes and a text box 2
Trying to remove error from conflicting code 0
Match style 0
Creating a Search using a combo box and then creating a report. 4
Dynamic Naming of Form Button 3
VBA formula strings longer than 255 characters 3
commandbutton does not answer 0
Link a text box based on drop down box with four options MS Word 365 0

Top