Add Records

[frankbutcher]

Hi All,

I have a form (frmProject) with a combo box for the cutomers field (list chosen from frmCustomers) . I also have a button to open frmCustomers if the relevant customer isn't in the list and so the record can be added to frmCustomers.
What I would like to be able to do is have a buttton on frmCustomers that automatically fills out the customers field on frmProject once it has been added as a record in frmCustomers.

Can anyone point me in the right direction or point me to a previous thread..........I have looked!!!

Thanks.
Frank.

 

[adam_fleck]

Maybe this might work

Suppose frmProject is based on a query that pulls in some additional details from the customer table such as address, telephone number, email…

All you have to do to get this extra customer info onto frmProject is to change the customerID. This is done via a combo: cboCustomerID, say.

So, on a button on frmCustomer, or on the form's close event, put code roughly like this:

Forms("frmProject")("cboCustomerID") = Me.customerID

There's a problem with this in that if frmProject isn't open when this is called it will cause an error, but you can protect against the error by using isFormOpen():

If isFormOpen("frmProject") then
	Forms("frmProject")("cboCustomerID") = Me.customerID
End If

isFormLoaded (from ghudson - http://www.access-programmers.co.uk/forums/showthread.php?t=49290&highlight=isloaded) is a user-defined function.

'Put this function in a public module

Function IsFormOpen(strFormName As String) As Boolean

    IsFormOpen = (SysCmd(acSysCmdGetObjectState, acForm, strFormName) = acObjStateOpen)

End Function

 

[frankbutcher]

Thanks!

Adam,
Thanks for the reply. Will try it out later.

Once again, thanks.

Frank.

 

[Rich]

Article ID: Q161007

 

[frankbutcher]

?

Adam,
Sorry to be Mr Thicko but...
1) The second code, where does that go?
2) Public Module......? If I create a new module, is that a public module? I am sure you can tell but I have never used them before!!

Many Thanks for your help.

Frank...

 

[adam_fleck]

module

Yep. Create a new module and paste the second bit of code into it.

I'm not sure why these things are sometimes called public modules, but maybe it's because the routines stored in them are available by default to all other objects in the database.

 

[frankbutcher]

I know I am a pain!

Sorry Adam, another question....

I added a button and put your first bit of code (Forms("frmProject")("cboCustomerID") = Me.customerID) to the on click function. Where does the second bit of code go? (If isFormOpen("frmProject") then etc etc)

Sorry, still learning...and a long way to go!!!

Thanks.

Frank..

 

[adam_fleck]

Sorry for the needless complexity. I was thinking that

If isFormOpen("frmProject") then
    Forms("frmProject")("cboCustomerID") = Me.customerID
End If

should go on your button's click event instead of just

    Forms("frmProject")("cboCustomerID") = Me.customerID

as this line on its own would cause an error if you were editing the customer form and frmProject wasn't open and you clicked the button.