Autofilling a value IF the user leaves null

[Laura1]

Hi all,
I am a newbie, and I've been struggling with this problem for days now. Every different way I try has an undesired side effect with the userability of my form... I have a bad feeling that this won't do what I want it to do.

Is there a way for me to have a field in my form/table autofill to the ID# of the table entry IF (and only if) the user does not enter a value in that field?

Any help is much appreciated...

 

[John Big Booty]

Can you explain in a little more details the structure of your form and underlying tables?

 

[Laura1]

You know, what I probably should be asking for is general help/advise in setting up my database... Here is what I'm trying to do:

I'm trying to setup a database that will track which raw materials/submodules are built into submodules/products at various levels of assembly. So I have 3 related tables:

KITTING: Records the workorder (WO)# and product being built
SMT: Records information about the workorder during the Surface Mount step (when components are added)
SMT_TRACK: Records which raw material / submodule is built into the workorder

There is a one-many relationship between KITTING and SMT, and a one-many relationship between SMT and SMT_TRACK

One problem is that the various products that will be entered into these tables don't all have the same information linked to them. Here is a list of all the different types of products that will be input into the KITTING table, and where they'll be used as inputs into both the SMT and SMT_TRACK tables:

COMPONENT W. BATCH - PO#, BATCH#, WAFER#. Data is used in SMT_TRACK table.
COMPONENT WO BATCH - PO#. Data is used in SMT_TRACK table
FLEX / PCB BASE - PO#
CERAMIC BASE WO#, BATCH#
IPD BASE WO#, BATCH#, WAFER#
DIE/WAFER PROCESSING WO#, BATCH#, WAFER#. Data is used in SMT_TRACK
SUBMOD, FLEX/PCB BASE: WO#, PO#. Data is used in both SMT and SMT_TRACK
SUBMOD, CERAMIC BASE: WO#, BATCH#, Data is used in both SMT and SMT_TRACK
SUBMOD, IPD BASE: WO#, BATCH#, WAFER#, data is used in both SMT and SMT_TRACK
MODULE, CERAMIC BASE: WO#, BATCH#, data is used in SMT
MODULE, FLEX/PCB BASE: WO#, PO#, data is used in SMT
MODULE, IPD BASE: WO#, BATCH#, WAFER#, data is used in SMT

Firstly, I'd like advise on the general setup of this database. Currently, I'd planned on having just the 3 tables (plus a lookup table for products). Since in my product list I have classified all products as per the list above, I can create queries to sort out each table based on which product when I need to. Any time I consider splitting the tables (ie having 2 KITTING tables, one for WO-based items and one for non-WO based items) I run into questions on how to handle them on the input to forms... (when I'm doing the SMT_TRACK form, I'd have to have separate tables there depending on which KIT table is being placed) It also makes me think the Queries/Reports I generate would become messy.

Right now I'm thinking keeping them in the same table is the right way to go. What do you advise?

The second issue I'm having is in allowing the operators to do entries into the SMT and SMT_TRACK forms. 95% of the time, it concerns products with WO#s. WO#s is how we do all our tracking internally. I would like to create the SMT and SMT_TRACK forms so that the operators can type in the WO#s into the form, but then the data that goes into the SMT or SMT_TRACK table field would be the ID# of the associated WO#. Is that possible?

In order to do that, I would need to have my original question answered, to have the table fill in any blank WO# entries with the ID# for that product for those products that don't have associated WO#s.

I know that I could create a combo box which would show the operators both the ID#, WO# and Product (for reference), but then they'd have to actually use the drop-down list to see that information. They could not simply type in the WO# into the field and have it look it up which is what I would prefer, since that is what they are accustomed to.

I apologize if I've not explained things clearly... (or too much) I'm only allowed to work on this project during my spare time, which usually ends up being my lunch hour, so it's been a bit slow for me absorbing all of this database stuff. Sadly I think I've gone in circles a few times as I try to figure it out, the solution may be obvious and I'm just missing it, or maybe it won't work...

Any help or advise you can provide is greatly appreciated!!!

 

[Laura1]

Thanks so much for posting... I tried that, but I must be formatting it incorrectly, because it's not working yet...

To explain my steps: I'm opening my Form in design view, selecting the field, opening the property sheet/Event/Before Update and in the line beside it typing:

= If IsNull([TLD_Kitting].[K_Workorder]) Then
[TLD_Kitting].[K_Workorder] = [TLD_Kitting].[K_TID]
End If

but access tells me:
"The expression you entered contains invalid syntax. You may have entered an operand without an operator". It also forces an "=" at the start of the expression.

I've tried adding a semicolon at the end of the End If;, but it gives the same error message: invalid syntax. I've tried a few different things, no luck.

Do you know what I'm doing wrong?

I'm going to post my request for advice on the database itself in another forum... I don't think the "Forms" one is quite right for that question.

Thanks again!!!