Populating combo box with info from unbound table

[flynbye]

Argh... here's hoping you guys can help. Once again I'm running into something that doesn't seem like it should be too difficult however "," hehe...

Here is my problem...
My customer has an open table brought up by selecting button in form which lists all the customers and their summed billing status (FCustomerBilling). Using this table he would like to double click on the customer number and position the combo box on the main form (FAccountsReceivable) which drives a series of subforms.

Here is hoping that this is easy... lol...
Thanks in advance for any help you can give!

 

[JimHorton]

flynby,
If the table is just an open datasheet--not a form--this isn't possible. You can mimick an open datasheet using a form in datasheet mode, then this would be easy to do. I highly recommend this, since it's generally not a good idea to give users access to raw tables, specifically because Access doesn't support very strong low-level validation, and you lose control of validation if users can open tables this way. Anyway, that said, if you open a form based on the table in datasheet (or any mode), you can then set the combo box on the other form in the doubl-click event of the textbox (here I'd recommend making a function and setting *all* textboxes double-click events to this function).

So create a function that basically says:

Forms!TheOtherForm!ComboBox = me.CustomerNumber
Forms!TheOtherForm.ComboBox_AfterUpdate 'force the after update (if you use it)
Note that you must force the afterupdate if you programattically set the combobox.
Also ote the dot above for the forcing of the afterupdate, and you MUST make the AfterUpdate Public to force this--which I'm assuming you use to set the subforms up.
--Jim

 

[flynbye]

Thanks Jim... I'll give that a shot and let you know how it turns out.

Chris

 

[flynbye]

Thanks Jim that was very helpful... and is working just fine although I'd like to check for an open form and then execute the code differently if form open or closed as I get an error otherwise.

I could have sworn I saw a reference to this somewhere but haven't had any luck running it down on the boards. My thought was to use the IsLoaded property from the AllForms collection. My problem is that I'd like a quick and dirty process and all my reference use extended code to get the answer I'm looking for.

Any thread references available or a code snippet that would return a true false? (Lord I hate being a beginning programmer... hehe) Thanks again and appreciate the assist.

-Chris

 

[flynbye]

Hmm... actually this is the code that I *thought* would work for this form... unfortunately I'm always getting a false reading for whether the form is loaded or not... assume that I'm formatting the IsLoaded property incorrectly but not seeing it... help

Private Sub Proposal___DblClick(Cancel As Integer)
On Error GoTo Err_Proposal_DblClick

Dim Status As Boolean
Dim stDocName As String
Dim stLinkCriteria As String

Status = CurrentProject.AllForms(FAccountsReceivable).IsLoaded
If Status = True Then
Forms!FAccountsReceivable!cboProposalID = Me.ProposalID
Else
stDocName = "FAccountsReceivable"
stLinkCriteria = "[cboProposalID] = " & ProposalID
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
Forms!FAccountsReceivable!cboProposalID = Me.ProposalID
End If

Exit_Proposal_DblClick:
Exit Sub

Err_Proposal_DblClick:
MsgBox Err.description
Resume Exit_Proposal_DblClick
End Sub

 

[JimHorton]

flynbye,
The way I check for a form loaded or not is simply do the following (I got in this habit back when there was no IsLoaded to use)

on error resume next
x = forms!FormImWonderingIfItsOpen.name
if err.number > 0 then
'then it's not open, act accordingly
docmd.openform " forms!FormImWonderingIfItsOpen"
Else
'do what you were going to do, since it's open
End if

You could get fancy and check for a certain error number, but the .name property is so safe that the only error I'd excpect to get when referencing the .name property is that the form doesn't exist in the forms collection, ie it's not open.

--Jim

 

[JimHorton]

flynbye,
In the above example, I overshot with the copy/paste---in the docmd.OpenForm, don't include the Forms! in the name...
--Jim