Combox and textbox

[HGoodloe]

Good afternoon,

I’m currently working on a project that involves master and subforms. On the master form I’m using a combox to populate related data in the subform.

Likewise on the master form, I’m also using a date textbox and command button to populate data in the subform based on a date typed in the textbox.

In addition, in the properties of the subform, I’m linking the date textbox(unbound) to the date_Scanned(bound) field to the subform.

So far everything seems to be working in regards to the Combo box and textbox populating the subform. However, if I select a value from the Combox the subform will populate and if I key in a date in the textbox and click the command button the subform will populate data corresponding to the date.

But, if I Key in date first to populate the subform and then need to make a selection from the Combo box, the subform will not populate.

As I previously stated, I have a legitimate reason for preferring using a textbox for date as opposed to a Combo box. As such, if I key in a date to populate the subform and then also need to select from the Combo box, I still need for the subform to populate. Subform should always populate when going back and forth between textbox and Combo box.

What kind of VBA code would be useful for what I’m trying to accomplish? I would greatly appreciate any help anyone out there can give.
Thanks so much…

 

[misscrf]

Can you please clarify if the combo box and the text form on the main form are both used to populate the same control on the subform or if they populate different fields? I am just trying to visualize this. A picture or field names might help to give some context.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Are you actually "populating" or are you filtering? Are you using link master/child? Is there some code you aren't sharing? Are you settin default values in the subform?

Duane
Hook'D on Access
MS Access MVP

 

[HGoodloe]

Yes, the combo box and text box which are on the main form will populate the same control on the subform or rather they will populate the same subform.

The only difference, in the combo box drop down when a particular document item is selected, the corresponding records will populate in the subform.

As For the text box, the same subform as well as same fiedls are populated based on a date keyed in the text box on the main form. In other words the subform will show all records corresponding to a date as opposed to document items populated from the combo box.

 

[dhookom]

populate" suggests you are either running code or an update query to edit values in a table. I haven't seen anything from you that suggests this is actually happening. Can you explain what's going on in the form/subform?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

A picture of the main form and subforms would be worth a million words. Either that or a list of field names from tables and control names from forms with their relationships would be necessary to understand what is going on.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

See demo

http://www.4shared.com/file/238667316/7183c53b/ComboOrText.html

2 methods: Linked subforms and applying a filter

 

[misscrf]

It looks like you have some code in the on enter. I would move that to the after update. You are not giving yourself a chance to enter in the information before you are trying to do an action as a result of putting the information in.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Good catch. I posted the wrong version.

http://www.4shared.com/file/239038657/848e85cd/ComboOrText_v11.html

This should demonstrate both methods. Most people probably would not use the linked subform method anyways.

 

[HGoodloe]

Ok, here's code for both the combo box and text box along with the subform fields.


Combo box code

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ScanDocID] = " & Str(Nz(Me![Combo4], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


Here are the subform fields that will populate:

Scanned_Research_Docs
NumOfPages
Date_Scanned

Here's the text box code on the main form stored in the command button.

Dim X As Variant

If Me.Text20 = "" Or IsNull(Me.Text20) Then
MsgBox "You must enter a date."
Me.Text20.SetFocus
Exit Sub
End If

If Not (IsDate(Me.Text20)) Then
DoCmd.OpenForm "frmSingleDate"
Me.Text20.SetFocus
Exit Sub
End If

X = DLookup("Date_Scanned", "VariousDocs", "Date_Scanned=#" & Me!Text20 & "#")
If IsNull(X) Then 'no match found
MsgBox ("Date not in database")
Else

Me.DocsSubformView.LinkMasterFields = "Text20"
Me.DocsSubformView.LinkChildFields = "Date_Scanned"

End If

As previously stated, both Combo box selection and data entered in the text box works just fine. However, if I type a date in the textbox on the main form to display records in the subform base on date, then if I need to select from the combo box the subform will not populate. If I select from combo box before entering a date in text box to populate subform it works, but not vice-versa.
I need for it to work either way.

 

[HGoodloe]

I'm not using an update query, just a simple select query.

Using a one to many relationship:

ScanVariousDocs main table or the "one" table... ScanDocID is the primary key

VariousDocs is the many table.

One of you suggested that I move code from on enter button or rather commmand button and place that code in the After Update Procedure. Well I already done that before emailing you guys but it didnt work.

As for the following link, I was unable to go to that page because among other web sites here at work, that page was blocked. Will have to wait until I get home this evening to check it out. This is the link that was sent:

http://www.4shared.com/file/238667316/7183c53b/ComboOrText.html

If this is a problem that can't be solved, I certainly understand and thank you guys for your efforts.