Solved Tabbed Form (1 Viewer)

[sctb0825]

I have a tabbed form. see screenshot img 1

My contact log in in a seperate table. see screenshot img 2

On my contact log table the agent cd and contact reason are lookup boxes (design view img 3)

Originally I had created a seperate form for contact log input img 4

What I would like is to have it all look like img 5 with the datasheet view inside the tab and fillable from that location.

I tried just adding the fields there but I am not doing something right cause I get an error saying the row is not committed.

Can someone give me some direction on this matter or a better way to do it.?

 

[theDBguy]

Hi. It might be better if you could post a sample db with test data.

 

[sctb0825]

Hi. It might be better if you could post a sample db with test data.

 

[Pat Hartman]

You need to create a form in continuous or DS view for the log. Then you can place it on the tab page.

PS,
1. using table level lookups is poor practice. I would get rid of them. They cause nothing but confusion. There are lots of warnings posted here regarding table level lookups. This a crutch for YOU. Once you know enough to create queries with joins, the TLL will start biting you. Best to get rid of them early rather than late. Combos on the form are great. Combos in the table are bad.
2. for the log table, change the PK to be just the autonumber. The AgentCD is just noise. If you want it to be unique, and I think you do, fix the PK so it is a single field index on the autonumber. Then add a separate unique index on AgentCD.
3. prefixing your column names just interferes with intellisense. I wouldn't use them.
4. having Captions on the table is not as bad as it used to be in earlier versions of Access but may still cause issues in VBA or queries. At a minimum, they are confusing since there are situations where you will see the actual column names rather than the caption names.

 

[sctb0825]

I read somewhere that too many querys was a bad practice also truth or fantacy

 

[June7]

It's bad practice to have multiple queries that pull same data with only difference of static filter criteria.

Pat, why would prefixes on field names interfere with intellisense?

 

[sctb0825]

understood any query that uses same table I just expand query and use it multiple times

 

[SHANEMAC51]

On my contact log table the agent cd and contact reason are lookup boxes (design view img 3)

you don't have a search by last name and agency - since you don't have any free fields

 

[Pat Hartman]

The prefixes interfere in the sense that you need to type them before you get to anything meaningful. Also, when you open tables/queries, the columns are around an inch wide so you can't see much more than the prefix. All in all, they don't bring anything to the table. If you like the concept, then use a suffix. The suffix doesn't get in the way of anything and serves to provide unique names if you have similar data in multiple tables (why?)

I read somewhere that too many querys was a bad practice also truth or fantacy

Like anything else. You need as many queries as you need. If you are organized in how you build queries, you can reuse them. I normally use the same query for a form/report/export to Excel. No need for three if the data is always the same. Also, as June mentioned, do NOT hard code selection criteria. Use unbound controls on forms to provide values. You don't get to make the criteria different using arguments. You just get to provide different values. So you can't change a query from searching for a Customer to searching for an address but you can reference a textbox or combo to search for ANY customer OR ANY address. Newbies make multiple queries for date ranges rather than one query with a start and end date which covers ALL variations.

 

[June7]

I seldom need or use dynamic parameterized queries. I apply filter criteria to form or report.

 

[Pat Hartman]

I seldom need or use dynamic parameterized queries. I apply filter criteria to form or report.

You must not ever use SQL Server as your BE then. The point of using SQL Server et al is to let the server do the heavy lifting. When you bind your forms/reports to tables or queries without criteria, you force Access to download entire tables from the server and process them locally.

 

[June7]

True. I developed a db at work with Access frontend and backend. The agency standard was Oracle and that would have meant coordinating with Oracle database manager located in another city. So, we stuck with Access to maintain local control. Oracle (or SQLServer) would have been overkill for our needs.

 

[Pat Hartman]

Sounds like it. The techniques for using a RDBMS for the BE differ from using Jet/ACE. That is why so many people are just floored when they convert their database to SQL Server and it is substantially slower than it was with Jet/ACE. All those filters forcing Access to drag down entire tables from the server Access and Jet/ACE are closely integrated so when BE is Jet/ACE, Access is in a better position to use the filters more efficiently.