Crashing

[Emma]

I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

 

[Emma]

Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Emma]

Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Emma]

OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Emma]

Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Emma]

Well, it's not crashing like it used to which is good. But it's crashing on
the macro I'm getting an 2950 error. It says The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. then it seems to stop debugging in the macro.

When i run it without the 1st form it just comes up blank.

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Emma:

I probably can't help you with this because I don't use macros and don't know
how they work. Somewhere in there is an ambigious reference that needs to
be made less ambigious.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Well, it's not crashing like it used to which is good. But it's crashing on
the macro I'm getting an 2950 error. It says The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. then it seems to stop debugging in the macro.

When i run it without the 1st form it just comes up blank.

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));


:

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Emma]

Is there something I need to do to join the module to the form?

Emma:

I probably can't help you with this because I don't use macros and don't know
how they work. Somewhere in there is an ambigious reference that needs to
be made less ambigious.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Well, it's not crashing like it used to which is good. But it's crashing on
the macro I'm getting an 2950 error. It says The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. then it seems to stop debugging in the macro.

When i run it without the 1st form it just comes up blank.

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));


:

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Actually no, there is nothing to join the two, exactly. Just to summarize,
here's the process:

1) Form 1 is opened and loads its current record.
2) The code from the Module is called by Form 1, updating the
global ClientID using the SetClientID() call
3) Form 1 button click requests the opening of Form 2
4) Form 2 has a hard coded query that filters itself for the last-saved
client ID which it gets by calling GetClientID()

Again, I am curious about what happens when you simply try to open
form 2. If you have replaced the reference to Form1 with the GetClientID()
function, then it should simply open, albiet to no valid record if the ClientID
has not been set. Can you confirm that this part works or is this where
you are getting the error?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Is there something I need to do to join the module to the form?

Emma:

I probably can't help you with this because I don't use macros and don't know
how they work. Somewhere in there is an ambigious reference that needs to
be made less ambigious.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Well, it's not crashing like it used to which is good. But it's crashing on
the macro I'm getting an 2950 error. It says The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. then it seems to stop debugging in the macro.

When i run it without the 1st form it just comes up blank.

:

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));


:

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Emma]

Ok I'm going to add more confusion in the box where I call the Client ID it
is : =Format([Session Start Date 2],"yyyy\:mm") & [Client ID]
I'm getting #Error here. The other box I have just has Client ID in it and
it has a green arrow in the corner of the field

Actually no, there is nothing to join the two, exactly. Just to summarize,
here's the process:

1) Form 1 is opened and loads its current record.
2) The code from the Module is called by Form 1, updating the
global ClientID using the SetClientID() call
3) Form 1 button click requests the opening of Form 2
4) Form 2 has a hard coded query that filters itself for the last-saved
client ID which it gets by calling GetClientID()

Again, I am curious about what happens when you simply try to open
form 2. If you have replaced the reference to Form1 with the GetClientID()
function, then it should simply open, albiet to no valid record if the ClientID
has not been set. Can you confirm that this part works or is this where
you are getting the error?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Is there something I need to do to join the module to the form?

Emma:

I probably can't help you with this because I don't use macros and don't know
how they work. Somewhere in there is an ambigious reference that needs to
be made less ambigious.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Well, it's not crashing like it used to which is good. But it's crashing on
the macro I'm getting an 2950 error. It says The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. then it seems to stop debugging in the macro.

When i run it without the 1st form it just comes up blank.

:

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));


:

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

Actually, that might end up being helpful.

Your query has two instances of [Client ID]. One is represented in the asterisk (*)
and one is in the Where clause. The SQL Below shows them both. From the
SQL view, you can simply delete the first reference below. In the graphical view,
you just uncheck the SHOW checkbox that flags the field as visible to the output.

This might also work, though I've never tried this sort of syntax.

=Format([Session Start Date 2],"yyyy\:mm") & [Tbl Client Information].[Client ID]


SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Ok I'm going to add more confusion in the box where I call the Client ID it
is : =Format([Session Start Date 2],"yyyy\:mm") & [Client ID]
I'm getting #Error here. The other box I have just has Client ID in it and
it has a green arrow in the corner of the field

Actually no, there is nothing to join the two, exactly. Just to summarize,
here's the process:

1) Form 1 is opened and loads its current record.
2) The code from the Module is called by Form 1, updating the
global ClientID using the SetClientID() call
3) Form 1 button click requests the opening of Form 2
4) Form 2 has a hard coded query that filters itself for the last-saved
client ID which it gets by calling GetClientID()

Again, I am curious about what happens when you simply try to open
form 2. If you have replaced the reference to Form1 with the GetClientID()
function, then it should simply open, albiet to no valid record if the ClientID
has not been set. Can you confirm that this part works or is this where
you are getting the error?

 

[Emma]

What is this lngID?

Ok I'm going to add more confusion in the box where I call the Client ID it
is : =Format([Session Start Date 2],"yyyy\:mm") & [Client ID]
I'm getting #Error here. The other box I have just has Client ID in it and
it has a green arrow in the corner of the field

Actually no, there is nothing to join the two, exactly. Just to summarize,
here's the process:

1) Form 1 is opened and loads its current record.
2) The code from the Module is called by Form 1, updating the
global ClientID using the SetClientID() call
3) Form 1 button click requests the opening of Form 2
4) Form 2 has a hard coded query that filters itself for the last-saved
client ID which it gets by calling GetClientID()

Again, I am curious about what happens when you simply try to open
form 2. If you have replaced the reference to Form1 with the GetClientID()
function, then it should simply open, albiet to no valid record if the ClientID
has not been set. Can you confirm that this part works or is this where
you are getting the error?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Is there something I need to do to join the module to the form?

:

Emma:

I probably can't help you with this because I don't use macros and don't know
how they work. Somewhere in there is an ambigious reference that needs to
be made less ambigious.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Well, it's not crashing like it used to which is good. But it's crashing on
the macro I'm getting an 2950 error. It says The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. then it seems to stop debugging in the macro.

When i run it without the 1st form it just comes up blank.

:

Emma:

It doesn't appear that the Query is causing the problem. I reformatted it below
and it seems to be fine.

What can you tell me again what the error was, and where you are getting it?
Also, can you simply open up form 2 without form 1 open, by simply double-
clickinig on it? (That's the idea behind the code we added.)

SELECT
[Tbl Client Information].[Client ID],
[Tbl Client Information].*,
[OLF Table].[In Default],
[OLF Table].[Called and Evicted]
FROM [Tbl Client Information]
LEFT JOIN [OLF Table]
ON [Tbl Client Information].[Client ID] = [OLF Table].[Client ID]
WHERE [OLF Table].[Client ID]=GetClientID();

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Oh yeh there's a tonne of information in the SQL here it is:

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].*,
[OLF Table].[In Default], [OLF Table].[Called and Evicted]
FROM [Tbl Client Information] LEFT JOIN [OLF Table] ON [Tbl Client
Information].[Client ID] = [OLF Table].[Client ID]
WHERE ((([OLF Table].[Client ID])=GetClientID()));


:

Ahhh. So the macro is on the button that opens the form. That doesn't
need to be touched in any way.

You get this error from the Query, not the form. Did you change the query
behind the second form in some way? Can you post the SQL for the query?

To view the SQL, choose SQL from the View menu while working on the
query in design mode.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


OK now I'm really confused because when I made the button I used a macro to
open the form. Now I'm getting the following error message: The specified
field [Client ID] could refer to more then one table listed in the FROM
Clause of your SQL statement. Do I need to say which table it's coming from
because I thought Me![Client ID] would be exclusive to the first form?

:

Emma:

I don't recall mention of a macro. Is the macro in one of the event's we're
referencing, like On Current or After Update? If so, then yes, it's functionality
must be moved to the event procedure, or ignored completely.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,

I tried to do everything you recommened however I still have the original
Macro, so now it's stopping somewhere in the macro and giving me an error
message. Should I delete the macro?

:

Emma:

Yes, this solution is probably too complicated, but once you "get it" you'll
see it's a cool way to save and use ID numbers.

First, create a new empty code module in the Access Modules area and
paste this code into it. Save the module with any name you wish.

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function


Now, on the form where ClientID is being set, the form referenced from
the query in the second form, put this code ...

Private Sub Form_Current()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Note that I'm assuming there is a text box named txtClientID. You
should put whatever name correctly identifies your text box with the ID
number in it. This is just the naming convention I use.

I'm using an Access function here named Nz() which will replace NULL
values with a zero (0). This will avoid unnecessary errors when a new
record is created and the Auto-ID hasn't been assigned yet.

So, we should add this code to the form's After Insert event also, to
capture the newly created ID numbers

Private Sub Form_AfterInsert()
On Error Resume Next
SetClientID Nz(Me!txtClientID, 0)
End Sub


Finally, we use the GetClientID() call in your query. I'm winging it here,
not knowing for sure what your query looks like, but it would be like this:

SELECT * FROM qryYourQuery WHERE [ClientID] = GetClientID()

If you're using the Visual QBE Grid to build your query, just put that call,
GetClientID() in the Where-Criteria row under the ClientID field.

Let me know if this wasn't clear on all points above.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny this sounds promising, however a little too complicated for me can
you break it down as I'm a dummy and need lots of intructions. Do I put this
in the Form code and what is the procedures name? A little confused, Emma

:

Is there any code behind the form? For example, is something being
executed in the form's Unload or Close events?

It doesn't seem likely that the query for the form's recordset would be
the source of issues with it closing.

Unless you are saying that you open a second form whose query is
based on the first form and then you close the first form. Yes, that
could cause a problem. If that needs to be the case, then save the ID
number in a global variable and build the query from that.

For example, put this code in a global module ...

Private m_ClientID As Long

Public Function SetClientID(ByVal lngID As Long) As Boolean
m_ClientID = lngID
End Function

Public Function GetClientID() As Long
GetClientID = m_ClientID
End Function

The first form calls the function, as below, in it's Current event.
SetClientID Nz(Me!txtClientID, 0)

The second form doesn't change, except it's query's WHERE clause
changes to read the ClientID from the function, not the form. Replace
[Forms]![Form Client Information Entry Form]![Client ID]

with the simpler GetClientID(). Now this query (and form) will work
even when the form isn't opened. You just have to remember to update
the global ClientID using SetClientID() call when appropriate.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I have a form (Form Client Information Entry Form) with 2 buttons on it for
two different types of loans. However on the latest loan form everything is
fine until I try to close the original form and the computer just freezes.
I'm using the following statement in the 2nd form's query. [Forms]![Form
Client Information Entry Form]![Client ID]. Any advice would be appreciated.

 

[Danny J. Lesandrini]

The reference to lngID is just a placeholder variable in the public function.
It could be named anything, but since its a Long Integer, I give it the prefix
lng and since it's an ID, its name becomes lngID.

Remember, you can/should have pasted that code into a public module
as it is, with no changes. After that, you can simply use the calls to set
and get the ClientID.

 

[Emma]

Hi Danny,

Everything is working great except for my Format clause it's coming up with
Name#? or Error# I want to thank you for helping me with the Big problem and
it does bring up the information even when the first form is closed, which is
great thanks.

 

[Danny J. Lesandrini]

You're very welcome.