Linking issues - newbie!

[BusMgr]

In order to limit table size, I've been asked to create multiple files (based on the job number) that have the same number of tables and table structure as my current db back end. I need to be able to create multiple new db back ends based on the job number entered.

Currently I have two forms, one to create a new job (with the job number) and another form to open a current job in progress.

I understand the concepts, but not the coding.

Any help would be appreciated.

 

[SBendBuckeye]

I'm not sure I understand what you are asking. Say you currently have a Job table, a JobBillingsHeader table and a JobBillingDetails table. Are you saying that you are being asked to create 3 new tables for each new job which would be identical in design and then you need to link to them out of common forms? Or are you saying you need to pull redundant data out of currently existing tables and create new normalized tables?

Good Luck!

 

[BusMgr]

What I have is close to your first scenario. I have a front end file that is my forms and reports. I have a backend file that is the tables with the data. What he wants is a separate backend file for each job based on the job number. At the end of each job, he wants to be able to update a master set of tables on the main office server with the various job files, but also keep a floppy with each individual jobs data in the respective paper folder. I tried to talk him into using replication, but because the jobs are scattered throughout the US, he decided to go this way. The operators can e-mail in the file and snail mail the floppy.

I got part way through it this afternoon creating the new backend database by using DoCmd.TransferDatabase (MS Knowledge Base Article Q210156) to create a duplicate file with the tables. Now I just have to complete the linking code based on the selected file. The new db is in the same folder (C:\Program Files\Job Info).

Thanks for your interest and help.

BusMgr

 

[SBendBuckeye]

So the idea is that they will start out separately, but at completion or some other point they will be rolled into master tables and the original individual files deleted?

Here are a couple ideas to get you started. If others have better suggestions, hopefully they will chime in.

I would keep a master table of currently open projects with customer number and job number in it. You could then present that to your users in a preliminary form and let them select which one they wanted to work with currently and then pass that infomation through OpenArgs to your main
form(s).

Then name all of your files identically with the customer number and job number appended to the table names. This does two things, it makes them unique and it allows you to very easily access the correct tables programatically.

As an example, say the Customer ID was 1234 and the job ID was 98765. You would then create tblJob123498765, tblJobDetails123498765, etc. If you build a standard set of tables to copy as you noted above, then all of your relationships, etc will be maintained for you automatically.

Then you can create SQL on the fly using the input from the user information mentioned above to get the correct tables and use that as the data source for your forms, etc.

As an example, assume the user selected Customer 1234 and Job 98765 as above from a combo box named cboJobInfo where that information is in columns 0 and 1 (zero based counting).

Then in the After Update event of cboJobInfo you could call your main form passing "RecordSource=123498765" in OpenArgs.

Dim strOpenArgs As String

strOpenArgs = "RecordSource=" & cboJobInfo.Column(0) _
& cboJobInfo.Column(1)

DoCmd.OpenForm etc, , , ,OpenArgs <- commas not counted

In your main program you could then have something like the following in the Load or Open event:

Dim strTableName As String
Dim strSQL As String
Dim strRecordSource As String

If IsNull(OpenArgs) Then
DoCmd.Close
Else
strRecordSource = Mid$(OpenArgs, InStr(OpenArgs, &quot;=&quot+1)
strTableName = &quot;BaseTableName&quot; & strRecordSource
strSQL = &quot;SELECT &quot; & strTableName & &quot;.* FROM &quot; _
& strTableName & &quot; WHERE SomeCriteriaIfAny &quot; _
& &quot;ORDER BY SortCriteriaIfAny &quot;, etc
Me.RecordSource = strSQL
End If

Hopefully this will get the old creative juices flowing and you'll end up with a very nice project out of this.

Good Luck!

 

[BusMgr]

SBendBuckeye

Thanks for your help. I used your ideas and got the project going. I still have some issues, but for the most part it does what it need to do.

Thanks again.