DoCmd.OpenForm (1 Viewer)

[Samantha]

Hi all, I am using 2013 and always learning. This should be really simple although all of the variations I have tried haven't worked out. I am sure it is something simple that I am overlooking. The following code is located on my form frmProposalLog which is a datasheet based on qryProposalLog containing tblProposals.ArchiveBox which is a text lookup field.

Private Sub ArchiveBox_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmBoxLocation", acNormal, , "[ArchiveBox]=" & Me.BoxNumber
End Sub

In theory when double clicking the field on my datasheet I would like frmBoxLocation to open matching tblProposals.ArchiveBox with tblLookupBoxLocation.BoxLocation.

 

[MarkK]

You haven't asked a question or described a problem.

 

[Samantha]

My apologies, the most important part "compile error method or data member not found".

 

[AlexN]

Hi all, I am using 2013 and always learning. This should be really simple although all of the variations I have tried haven't worked out. I am sure it is something simple that I am overlooking. The following code is located on my form frmProposalLog which is a datasheet based on qryProposalLog containing tblProposals.ArchiveBox which is a text lookup field.

Private Sub ArchiveBox_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmBoxLocation", acNormal, , "[ArchiveBox]=" & Me.BoxNumber
End Sub

In theory when double clicking the field on my datasheet I would like frmBoxLocation to open matching tblProposals.ArchiveBox with tblLookupBoxLocation.BoxLocation.

You want to match tblProposals.ArchiveBox with tblLookupBoxLocation.BoxLocation but in your code you refer to Box.Number

 

[Samantha]

tblLookupBoxLocation.BoxLocation.

was an type-o that is one of the fields on the form that is opening. tblLookupBoxLocation.BoxNumber should match with ArchiveBox.

Since ArchiveBox is a lookup in tbleProposals how do I determine what it is actually saving? The record source for the field in the underlying table is actually a number not text? So, in actuality it is saving the tblLookupBoxLocation.ID although it is displaying the tblLookupBoxLocation.BoxNumber?? please confirm

If that is the case then I need to change it to refer to the ID field instead?

 

[AlexN]

was an type-o that is one of the fields on the form that is opening. tblLookupBoxLocation.BoxNumber should match with ArchiveBox.

Since ArchiveBox is a lookup in tbleProposals how do I determine what it is actually saving? The record source for the field in the underlying table is actually a number not text? So, in actuality it is saving the tblLookupBoxLocation.ID although it is displaying the tblLookupBoxLocation.BoxNumber?? please confirm

If that is the case then I need to change it to refer to the ID field instead?

Of course it saves the ID (number, first column of the lookup source) by default. Lookup fields are always numbers.
That's a reason why you shouldn't use lookup fields in tables. Use them in the data entry form only.

 

[Samantha]

Use them in the data entry form only.

How do you accomplish this? I was under the impression that the information does not save anywhere when you have a unbound control. Unless I have this confused?

Back to the original problem at hand - so I changed the code as follows referring to the id field

Private Sub ArchiveBox_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmBoxLocation", acNormal, "[ID]=" & Me.ArchiveBox
End Sub

No error code is produced the form opens to the first record.

 

[AlexN]

How do you accomplish this? I was under the impression that the information does not save anywhere when you have a unbound control. Unless I have this confused?

It isn't unbound. It keeps its ControlSource Property but you define its RowSource after changing it to a combobox. You could find enough information here http://support.office.com/en-ca/art...ombo-box-70abf4a9-0439-4885-9099-b9fa83517603

Back to the original problem at hand - so I changed the code as follows referring to the id field

Private Sub ArchiveBox_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmBoxLocation", acNormal, "[ID]=" & Me.ArchiveBox
End Sub

No error code is produced the form opens to the first record.

Check again the field properties of the controls that must much. If Archive.Box is a lookup field try using ArchiveBox.Value in your code.

 

[Samantha]

Thanks for your willingness to help Alex, I changed it to .value and still no error code, opens to the first record.

changing it to a combobox

Ahh...I think after the headache on something so trivial I will abandon the look up fields and start replacing them in my tables as you have suggested. There are many throughout my db. I find that each time I try to add a functionality to the db they create a mess for me. Live and learn right!

Best wishes,
Samantha