Form field data from another form field

[DaleG]

Hello -



First, yes I know this is an elementary question. I have read and read and googled and searched and can't find a working solution. I think everything I see is more complicated than I need. I know my question/goal will most likely not meet proper db design and solutions protocols, but there is a reason. I will explain in detail if you want to read, or you can skip this next paragraph if you really don't care.

Begin long boring explanation - Our company decided to transition to a new software platform. Our old platform was a highly customized IBM Notes platform that handled cradle to grave for a sales cycle, transitioning to manufacturing cycle, then to service contracts. One thing it never included though were our 3rd party service contracts. I have manually done those for years. So along comes what is supposed to be the latest and greatest answer to all our problems. And the company decides they want it to work exactly like the process flow we'd developed in Notes over the last 2 decades. So, here we go - customizing again. Well, we get the sales cycle and implementation cycle done - and boom. Our in-house programmer moves to greener pastures. Our service contracts never made it to the new system so they are still being handled out of the old system. Since we are mid-migration (which has been 18 months now, and promises to be at least that long again), they'd made the call that no more development would occur in the Notes platform. So that means I am looking at nearly 2 years minimum before our 3rd party service processes get reviewed for inclusion. Now, a lot of the information that is needed for the 3rd party contracts are contained within implementation records. With the new software, which is web based, I can download a report in to excel that is a datasheet of all implementations in process, its number (which is the primary key), all of the info about it, etc. But what is nowhere to be found in the entire software system - is a place for me to identify what service provider will handle the support for any particular implementation, if the contract was sent, the system configuration, the coverage level of support, etc. That is what got me to where I am.

I am tired of maintaining a separate document for every maintenance order (service contract) I send to a service provider. I need a database for the info. Since it is not going to be happening in the real system for a long time, I decided to build my own. I am well-versed in excel, and just good enough at access to get in trouble. So what I have is a report in the new software that I export to excel. I then open it and saves as an existing document that is linked to my access db. That way I can refresh my data in the linked table easily enough. Then in access, I have a series of update and append queries to refresh my overall data. I have a form that gives me fields not in the original data that I need ( service provider, contract sent checkbox, notes about the account, a retention checkbox ( after the implementation is closed in the actual software the record will no longer be on future updates - so this is if there is one I want to keep in my recordset after its completed for some reason), and case # for the support case ticket opened for the installation of a new product as part of the service. This all works great. It helps me track when systems need installed and which ones are coming up soon. Then I decided to create a form to enter the details for an actual service contract to the service provider. This has a lot more data input because what is needed doesn't appear where it can be downloaded. So I created the form, imported part numbers, pricing, etc. and have that all working. I can easily view upcoming implementations based on a linked dataset. I can easily create a maintenance order based on the implementation number, and all this data gets saved and stored in my database. -End long boring explanation


Now I want to get lazy and this is where I need help. I want to put a button on the form for "Implementation Review" so that when I click it, it will either (a) return the form record of the maintenance order from frmMaintOrd with the matching implementation number, or (b) if one does not exist, open a new form and pre-fill the implementation number and customer name based off of the record I am on in the implementation review form. My field names are:

ImpNum = Implementation Number, this is a unique number on the Implementation detail in the linked table as well as the maintenance table
CusName = customer name
frmMaintOrd = the name of the form where i enter the maintenance order details
frmImpRev = the name of the form where i do my review of upcoming implementations (this is a form and not a report because I have additional fields that I can update back to a different table that stays after the linked table is updated/refreshed)
tblImpData = the linked table that provides most of the info for my review
tblAddInfo = the table of additional info that I add that needs to be retained after I update/refresh tblImpData
tblOrdDetail = details of the maintenance orders created from frmMaintOrd


My mind sees this as :

A button on a record in frmImpRev, that when clicked, will:

Look to frmMaintOrd for a record where ImpNum = ImpNum on the current frmImpRev record, then open to it.

If one is not found, new record on frmMaintOrd and make ImpNum and CusName = ImpNum and CusName on the current frmImpRev record.

I assume that goes beyond what a macro can do so it would need to be VBA, which I barely even know how to spell much less work with...


Sorry for the long post, I have read plenty of times where people don't give enough info as to why they are trying to do what they are trying to do and wanted to try and avoid that. Again, this is a way for me to keep up with my items and won't be used by anyone else but me. Its a stop-gap to get me through until all of this is customized in to our real operational software system.

Thank you,
Dale

[DaleG]

I did find a work around of sorts. I mentioned that I have a series of update/append/delete queries that massage the data coming in from the linked table to save certain pieces of info, and I have a button that runs the queries in sequence to write to tables certain info I need to retain. I was able to create an append query that basically creates a record on the table that holds the maintenance order details to add a record for every implementation number that exists on the linked table. It appends the implementation # and customer name, but the other fields are obviously blank since they don't have active orders. Then I can put a button on my frmImpRev that takes me to the matching record in frmMaintOrd (since now all implementation #'s have a record, even if its a blank record). From there I can add the rest of the order info.

The problem with this method is that it creates a ton of records I don't need. Not every Implementation # will result in a maintenance order. Some items are software upgrades, which we don't create a maintenance order for. Some are supported by our own field service team, so we obviously wouldn't write ourselves an order. So while it works, its clumsy at best. If there isn't another solution, I'll deal with it, but I'd rather be able to auto create a record when needed rather than have a load of rubbish in my table.

Thanks again,
Dale

[June7]

What you really want is likely possible with enough code. Possibly will involve a DLookup() domain aggregate function and If Then Else conditional branching.

If you want to provide db for analysis, follow instructions at bottom of my post.