Subform populating main form controls on ID Change

[IGR]

Dear community members:

I got stuck at this code --> On the main form the user selects three parameters -> clicks on show record, which prompts a query to run.

Based on the parameters the query extracts into a view table product ID number.

The ID number is fed into ID listbox on the main form. The ID listbox has SQL query to select ID field from the view table. The reason I needed this step is that it may happen that there are 2 records with same parameters which need to be changed one at a time.

Further, the user would click on one of the IDs in the listbox on the main for the below code would fire, displaying the record in the subform and populating the controls on the mainform with data to be edited.

But, if the user changes his mind and clicks on the second ID, I am getting an error, starting with the first line of the code me.txtsDate.

Private Sub ID_AfterUpdate()
Me.Requery
Me.Form.Refresh
With Me.frmWOW_sub.Form.Recordset
Me.txtsDate = .Fields("Start Date")
Me.txteDate = .Fields("End Date")
Me.txtcCount = .Fields("Club Count")
Me.Text416 = .Fields("Article Number")
Me.Combo399 = .Fields("Department")
Me.Combo401 = .Fields("Category")
Me.txtsArticle = .Fields("Sister Article Number")
Me.txtaDescription = .Fields("Article Description")
Me.txtRetail = .Fields("Retail $")
Me.txtMargin = .Fields("Margin %")
Me.txtMarketing = .Fields("Marketing")
Me.Refresh
End With
End Sub

My steps to approach the problem were as follows:

- insert Me.txtsDate = "" command types to clear the main form, before the codefires. However I came to realize that even if the form clears the ID control needs to remain populated with ID options for the user to chose from.
- even if clearing the ID box, the subform does not requery or empties.

Don't seem to have a clue of any other potential problems, but can't find the issue either.

Please help!

 

[spikepl]

1. Use blank lines and spacing to make your big black blob readable for others. If you do that I might read it.

2. Me.txtsDate = "" is not valid if that control contains dates - you cannot put a date equal zero-length string as "". Use Null.

3. What error do you get - do not keep the error message secret.

4. Why do you use the subform's recordset? That recordset is positioned on record one, not the same as the subform currently. To get data from current subform, just use the values held in the controls on the form.

 

[IGR]

Just edited my post as per Spikepl request:

2. Changed the me.txtsDate and me.txteDate to = Null

3. What error do you get - do not keep the error message secret.
- I am still getting the error "The value you entered isn't valid for this field"

4. Why do you use the subform's recordset?
- I deleted the recordset command, but no changes.

 

[IGR]

Tried the following:

Private Sub ID_AfterUpdate()
Me.Requery
Me.Form.Refresh
Me.txtsDate = .Fields("Start Date")
Me.txteDate = .Fields("End Date")
Me.txtcCount = .Fields("Club Count")
Me.Text416 = .Fields("Article Number")
Me.Combo399 = .Fields("Department")
Me.Combo401 = .Fields("Category")
Me.txtsArticle = .Fields("Sister Article Number")
Me.txtaDescription = .Fields("Article Description")
Me.txtRetail = .Fields("Retail $")
Me.txtMargin = .Fields("Margin %")
Me.txtMarketing = .Fields("Marketing")
Me.Refresh
end sub

When the first record is selected, the controls on the main form are populated fine with record data from the subform.

However when clicking on the second record in the ID box, the following error comes up: "Invalid or unqualified reference"