Referencing field on Main form to save to Subform bound table

[Design by Sue]

I will see if I can trim this down and post - I am so stumped!

Sue

 

[boblarson]

Well, this certainly would:

Private Sub Description_GotFocus()

If IsNull(Me.Parent![P Description]) Then
MsgBox "You must enter choose a prepack before you can add items.", vbExclamation, "Information Needed"
[B][COLOR=red]Me.Parent![P Description].SetFocus
[/COLOR][/B]Exit Sub
End If

End Sub

 

[Design by Sue]

The reason for that is to ensure the user selects the Pack on the main form and I thought it should only have an effect if the P Description is blank.

Sue

 

[Design by Sue]

I tried removing that code and it made no difference - still same problem.

Sue

 

[Design by Sue]

I don't remember how to upload the file - I have a trimmed database.

Sue

 

[boblarson]

I don't remember how to upload the file - I have a trimmed database.

Sue

How to upload a database to the forum

 

[Design by Sue]

I found out that it was because I had put the master child links in the subform. That was causing the immediate save. So I am back to the original problem - how to reference the field on the main form from the sub form

Sue

 

[boblarson]

Okay, a couple of things so far.

1. Putting a save button in the subform isn't going to do much except it can save the subform record. There is no need to use DoCmd.RunCommand acCmdSaveRecord as just moving to the new record will save.

2. You should not have Input masks on controls that have an expression bound to them. Use the FORMAT property to format it but the input mask shouldn't be there because you can't type something in anyway.

3. You had the description combo box in the subform bound to the wrong column. You had it bound to 1 when it should be 3 unless you moved ITEMID to the first column.

4. Take a look at the Master/Child links. The Master (because it is to an unbound form - yes I reverted to that) needs to have it entered manually as I did.

5. The too complex error can be eliminated after doing the work to the subform and then deleting it from the main form and then adding it back in again. Remember to do that before you change your links.

6. I would suggest going into Access Options and under Current database uncheck the Perform Name Auto Correct.

Okay, here's a working copy back.

 

[boblarson]

I found out that it was because I had put the master child links in the subform. That was causing the immediate save. So I am back to the original problem - how to reference the field on the main form from the sub form

Sue

No that wasn't the problem. See my sample I just uploaded for a working copy.

 

[Design by Sue]

Thank you - I will download and try to clean up my mess!

Bob - you have the patience of a saint!

Sue

 

[Design by Sue]

1 - thanks - do you know why when going to a new record the description field doesn't reset to blank? and how can I make to do so? I thought going to a new record did that.

2 - duh (on my part)

3 - another duh (also on my part)

4 It appears my biggest error was the link to the master should have been [Forms]![Combinations FRM]![Prepack ID] - I found a chart on line of how to refer from one to another and I should have checked this!

5 good advise for the future

6 - will try to find that.

Sue

 

[boblarson]

1 - thanks - do you know why when going to a new record the description field doesn't reset to blank?

Quick question -

Why would you have the description go to blank after adding each item? Will there not be multiple items for each of the Prepacks? Having the user select that for each one seems a little unfriendly. They can then choose another prepack to add items to if they wish. And you can set the prepack control on the subform to hidden so it doesn't show as I had it there to verify that it had the right one. When you click save and then select another prepack from above, it doesn't change right away until you select an item in the subform, which is fine but may confuse people.

 

[Design by Sue]

1 Not the description of the prepack - that is acting exactly as I want it to - the item description on the subform stays selected when I click Save (now go to new record)

2 Is the prepack control on the subform necessary - can I delete it - or should it just be made not visible?

 

[boblarson]

1 Not the description of the prepack - that is acting exactly as I want it to - the item description on the subform stays selected when I click Save (now go to new record)

It doesn't on my computer in the sample. But now I think I know why. You had it as unbound but it needs to be bound to ITEMID so I had to change your bound column from 1 to 3 on it.

2 Is the prepack control on the subform necessary - can I delete it - or should it just be made not visible?

I said in my last post to hide it.

 

[Design by Sue]

OK - I just did what I should have before and just exported your forms to my complete database and the description on the subform is going blank as it should.

I am creating the list of items already included in the pack in the list box on the form. I had the query working previously, but now I can't link it to the pack id. What I had used before was Like [Forms]![Combinations FRM]![Prepack ID] - what would the correct code be?

 

[Design by Sue]

Got it - I was able to link it to the text field on the subform

Thanks again for your help today

Sue

 

[Design by Sue]

Actually that refreshes too late in the process - so my last question of the day (yes I mean it because I am leaving shortly) is how do I link a query to the text box that is the Prepack ID on the Main Form? That number is actually column 1 of the description combo box row source. When I tried to build the like statement to the prepack id in the main form there were no results, even though there are results in the table.

I can't search on the description itself as there is a compound index (think that is what it is) that combines the description and the product group to prevent duplicates, so the description itself has duplicates. Hope that makes sense.

 

[KiEESH]

I have code that uses a form/subform combo to do a a search & view, respectively.
However, I need to view & edit after the search is performed.
As such, I'd prefer to have a table be the subform's record source, but I'm not sure how to alter the code to search the table.

I ultimately need to update the table using partial/whole user search input.

I'd consider an update query, if that's easier, but still unsure of how to incorporate.

Code in question highlighted.

Any ideas would be appreciated.

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me!cmbZONE = ""
Me!txtBLDG_NUM = ""
Me!cmbCOMPASS_PT = ""
Me!txtSTREET = ""
Me!cmbATERY = ""


End Sub

Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub
Private Sub btnSearch_Click()

' Update the record source
Me!frmsub_EditBldg.Form.RecordSource = "SELECT * FROM qry_AREA_GROWTH2 " & BuildFilter

' Requery the subform
Me!frmsub_EditBldg.Form.Requery


End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter


' Check for ZONE
If Me!cmbZONE > "" Then
varWhere = varWhere & "[ZONE] = " & Me.cmbZONE & " AND "
End If

' Check for LIKE BLDG_NUM
If Me!txtBLDG_NUM > "" Then
varWhere = varWhere & "[BLDG_NUM] LIKE """ & Me.txtBLDG_NUM & "*"" AND "
End If

' Check for COMPASS_PT
If Me!cmbCOMPASS_PT > "" Then
varWhere = varWhere & "[COMPASS_PT] = " & Me.cmbCOMPASS_PT & " AND "
End If

' Check for LIKE STREET
If Me!txtSTREET > "" Then
varWhere = varWhere & "[STREET] LIKE """ & Me.txtSTREET & "*"" AND "
End If

' Check for ATERY
If Me!cmbATERY > "" Then
varWhere = varWhere & "[ATERY] = " & Me.cmbATERY & " AND "
End If


' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function