Form not populating

[Emma]

I have a form which is suppose to populate the client information and has 2
sub datasheets. Unfortunately it isn't working any more. It was working
perfectly. I open the form using a macro which pulls the client information
from the main form. Here's the OpenForm code Testing Loans, Form, , [Client
ID]=[Forms]![Form Client Information Entry Form]![Client ID], , Normal.

All I have changed is that I added an eviction checkbox, which comes froom
the main table, but isn't on the main form. Is this the problem or is it
because I've changed the permissions?

Thanks Emma

 

[Steve Schapel]

Emma,

What exactly does "isn't working any more" mean? The form opens with no
data? The form opens with the wrong data? The form doesn't open? You get
an error message? Something else? Thanks.

 

[Emma]

The form opens with no data

Emma,

What exactly does "isn't working any more" mean? The form opens with no
data? The form opens with the wrong data? The form doesn't open? You get
an error message? Something else? Thanks.

--

Steve Schapel, Microsoft Access MVP

I have a form which is suppose to populate the client information and has
2
sub datasheets. Unfortunately it isn't working any more. It was working
perfectly. I open the form using a macro which pulls the client
information
from the main form. Here's the OpenForm code Testing Loans, Form, ,
[Client
ID]=[Forms]![Form Client Information Entry Form]![Client ID], , Normal.

All I have changed is that I added an eviction checkbox, which comes froom
the main table, but isn't on the main form. Is this the problem or is it
because I've changed the permissions?

Thanks Emma

 

[a a r o n _ k e m p f]

Jet randomly stops working.

Move to SQL Server if you give a shit about your data.

-Aaron

Emma,

What exactly does "isn't working any more" mean?  The form opens with no
data?  The form opens with the wrong data? The form doesn't open?  You get
an error message? Something else?  Thanks.

--

Steve Schapel, Microsoft Access MVP

I have a form which is suppose to populate the client information and has
2
sub datasheets. Unfortunately it isn't working any more. It was working
perfectly. I open the form using a macro which pulls the client
information
from the main form. Here's the OpenForm code Testing Loans, Form, ,
[Client
ID]=[Forms]![Form Client Information Entry Form]![Client ID], , Normal.

All I have changed is that I added an eviction checkbox, which comes froom
the main table, but isn't on the main form. Is this the problem or is it
because I've changed the permissions?

Thanks Emma

 

[Steve Schapel]

Emma,

Can you please try this... if the Testing Loans form is based on a query,
we'll use the query. If it's based on a table, make a query based on that
table.

Either way, in design view of the query, enter [Forms]![Form Client
Information Entry Form]![Client ID] in the Criteria of the Client ID column.
Then, with the Form Client Information Entry Form form open, run the query
and see if it returns the expected results.

 

[Emma]

Here's the original record source SELECT [Tbl Client Information].*, [Loans -
Table].[In Default], [Loans - Table].[Called and Evicted]. I'll now try
creating a query and let you know what happens.

Emma,

Can you please try this... if the Testing Loans form is based on a query,
we'll use the query. If it's based on a table, make a query based on that
table.

Either way, in design view of the query, enter [Forms]![Form Client
Information Entry Form]![Client ID] in the Criteria of the Client ID column.
Then, with the Form Client Information Entry Form form open, run the query
and see if it returns the expected results.

--

Steve Schapel, Microsoft Access MVP

The form opens with no data

 

[Steve Schapel]

Emma,

I'd be pretty sure that's not the entire Record Source.

 

[Emma]

Sorry something wonky happened there!

Here's the original record source
SELECT [Tbl Client Information].*, [Loans - Table].[In Default], [Loans -
Table].[Called and Evicted]
FROM [Tbl Client Information] INNER JOIN [Loans - Table] ON [Tbl Client
Information].[Client ID] = [Loans - Table].[Client ID];

Emma,

I'd be pretty sure that's not the entire Record Source.

--

Steve Schapel, Microsoft Access MVP

Here's the original record source SELECT [Tbl Client Information].*,
[Loans -
Table].[In Default], [Loans - Table].[Called and Evicted]. I'll now try
creating a query and let you know what happens.

 

[Steve Schapel]

Emma,

Ok, so make a query like this:

SELECT [Tbl Client Information].*, [Loans - Table].[In Default], [Loans -
Table].[Called and Evicted]
FROM [Tbl Client Information] INNER JOIN [Loans - Table] ON [Tbl Client
Information].[Client ID] = [Loans - Table].[Client ID]
WHERE [Tbl Client Information].[Client ID] = [Forms]![Form Client
Information Entry Form]![Client ID]

.... and with the [Forms]![Form Client Information Entry Form] form open, try
to run the query, and see what you get.

 

[Emma]

Hi Steve,

I don't know how to make this into a query

Thanks Emma

Emma,

Ok, so make a query like this:

SELECT [Tbl Client Information].*, [Loans - Table].[In Default], [Loans -
Table].[Called and Evicted]
FROM [Tbl Client Information] INNER JOIN [Loans - Table] ON [Tbl Client
Information].[Client ID] = [Loans - Table].[Client ID]
WHERE [Tbl Client Information].[Client ID] = [Forms]![Form Client
Information Entry Form]![Client ID]

.... and with the [Forms]![Form Client Information Entry Form] form open, try
to run the query, and see what you get.

--

Steve Schapel, Microsoft Access MVP

Sorry something wonky happened there!

Here's the original record source
SELECT [Tbl Client Information].*, [Loans - Table].[In Default], [Loans -
Table].[Called and Evicted]
FROM [Tbl Client Information] INNER JOIN [Loans - Table] ON [Tbl Client
Information].[Client ID] = [Loans - Table].[Client ID];

 

[Steve Schapel]

Emma,

In Access 2007 go to Create=>Query Design.
In earlier version, go to Queries=>New=>Design View

Close the 'Show Table' dialog without adding any tables to the query design
panel.

In Access 2007 click the SQL button on the query design ribbon.
In earlier version go to View=>SQL View

Copy/paste the SQL string I gave you into the SQL window.

Change back to Design view of the query.

With the [Forms]![Form Client Information Entry Form] form open, run the
query via the red [!] button, and see what you get.

 

[Emma]

Hi Steve,

I set the record source to the new query, but still the form is coming up
blank?

Thanks Emma

Emma,

In Access 2007 go to Create=>Query Design.
In earlier version, go to Queries=>New=>Design View

Close the 'Show Table' dialog without adding any tables to the query design
panel.

In Access 2007 click the SQL button on the query design ribbon.
In earlier version go to View=>SQL View

Copy/paste the SQL string I gave you into the SQL window.

Change back to Design view of the query.

With the [Forms]![Form Client Information Entry Form] form open, run the
query via the red [!] button, and see what you get.

--

Steve Schapel, Microsoft Access MVP

Hi Steve,

I don't know how to make this into a query

 

[Emma]

I changed the inner join and it's working great now, Thanks Steve!

Emma,

In Access 2007 go to Create=>Query Design.
In earlier version, go to Queries=>New=>Design View

Close the 'Show Table' dialog without adding any tables to the query design
panel.

In Access 2007 click the SQL button on the query design ribbon.
In earlier version go to View=>SQL View

Copy/paste the SQL string I gave you into the SQL window.

Change back to Design view of the query.

With the [Forms]![Form Client Information Entry Form] form open, run the
query via the red [!] button, and see what you get.

--

Steve Schapel, Microsoft Access MVP

Hi Steve,

I don't know how to make this into a query

 

[Steve Schapel]

Ok, that's great Emma.

 

[a a r o n _ k e m p f]

that means your query doesn't have any rows.

try a looser where clause.

PS - if you were just using SQL Server, it would be a lot easier to
run SQL profiler to see what the actual SQL Statement is (once it gets
through your code)

Hi Steve,

I set the record source to the new query, but still the form is coming up
blank?

Thanks Emma

Emma,

In Access 2007 go to Create=>Query Design.
In earlier version, go to Queries=>New=>Design View

Close the 'Show Table' dialog without adding any tables to the query design
panel.

In Access 2007 click the SQL button on the query design ribbon.
In earlier version go to View=>SQL View

Copy/paste the SQL string I gave you into the SQL window.

Change back to Design view of the query.

With the [Forms]![Form Client Information Entry Form] form open, run the
query via the red [!] button, and see what you get.

--

Steve Schapel, Microsoft Access MVP