Updating Two Check Boxes on a Form from A Choice in another Combo Box

[chrisc]

Hello,

My infamous manufacturing database continues to bane my life, mostly
because I'm really stumbling around in the dark here!

What I am trying to do is populate some fields automatically dependent
on choices made elsewhere, I'll set the scene:

Users take a PCB off of the production line and put it through our
testing mechanisms. At present it is logged on a peice of paper and
then exceled into a report, which takes ages and is rubbish.

Now they will have this database I am 'attempting' to create to do the
hard part for them, i.e. using a pen and creating reports.

Problem 1:

They need to log the fault type from a list of 6 possible problems, one
of these is 'Pass'. If this is chosen then I want the Pass tickbox at
the end of the row to be checked, and the Fail one to be un-checked.

If anything else is chosen then the reverse of this should happen.

So far the code I've got in for the afterupdate event on the combobox
is this:

Private Sub Fault_Description_AfterUpdate()
If Me![Fault_Description] = Pass Then
Me![Pass] = -1 And Me![Fail] = 0
Else
Me![Pass] = 0 And Me![Fail] = -1
End If
End Sub

I've tried a few variations of this, but so far nothing, and as you're
probably now aware I have NO idea what I'm doing, except for a basic
grasp of the concepts!

Problem 2:

I wanted to create the number of records on my continous form
automatically for the user, and numbered in the 'serial_number' field
as well. So the user enters '50' into a box somewhere, and hits a
button which automatically adds 50 records on my continuous faults
subform against my batch number and user name on the main form.

I've got some distance with automatic numbering using the dmax funtion,
but it just doesnt cut it. The problem is the next user who comes
along would be using serial numbers 51-100, and this is of no use, it
has to start from 1-x again as the first test would have.

I realise I'm asking a shed load here, so any advise other than 'buy a
book' which I've already done several times! Would be much appreciated.

Cheers,

Chris.

[Rog]

1. If Fault_Description is a text field, you need to put "Pass"
between quotation marks. (BTW, why do you have a Pass and a Fail
checkbox? Does an item not automatically fail if it does not pass, and
vice versa?)

2. You need to use a loop to create records, e.g. (air code)

dim mydb as database, myset as recordset, x as long
set mydb = currentdb()
set myset = mydb.OpenRecordset("tblFaults",dbOpenDynaset)
for x = 1 to Me!myNumberBox
myset.AddNew
myset("SerialNumber") =x
myset.Update
next
myset.close
set myset=nothing
set mydb=nothing

[chrisc]

I think i'm way out of my depth here, I dont really know much about
VBA.

The example I gave above doesnt work at all, even with "pass" written
into it.

Instead of the field names I'm trying to change I've also tried
referencing the check boxes on the form directly, but that doesnt work
either..

And then I've tried the code that Rog put above for the other things I
want to do and just get compile errors!

[chrisc]

Ok, I've got a little further with my 2nd problem, the reason I have 2
tick boxes by the way is to do with the reports side not liking it when
I only have one and counting the totals..

As for the 2nd prob:

Private Sub Command38_Click()

Dim mydb As database, myset As Recordset, x As Long
Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("Faults", dbOpenDynaset)
For x = 1 To Me!AutoNum
myset.AddNew
myset("SerialNumber") = x
myset.Update
Next
myset.Close
Set myset = Nothing
Set mydb = Nothing

End Sub

Gets to the third line before giving me a type mismatch error - could
this be to do with the recordset name? As I just put the table name in
that its referring too, in this case 'Faults'

As usual, help much appreciated in advance :)

Chris.

[Tim Marshall]

ch****@cemgraft.co.uk wrote:

Problem 1:

Private Sub Fault_Description_AfterUpdate()
If Me![Fault_Description] = Pass Then
Me![Pass] = -1 And Me![Fail] = 0
Else
Me![Pass] = 0 And Me![Fail] = -1
End If
End Sub
This is being done on a form, I presume.

To do exactly what you seem to be describing above, do the following:

Private Sub Fault_Description_AfterUpdate()

If Me![Fault_Description] = "Pass" Then

Me![Pass] = -1
Me![Fail] = 0

Else

Me![Pass] = 0
Me![Fail] = -1

End If

End Sub

However, why not just use an option group (fraPassFail in the example
below) with two check marks? You could have the "pass" check mark have
a value of 1 and the "fail" check mark have a value of 2 and then,
presumeably, this information would be stored in a table in which the
field names are pass and fail (I generally use the dot. notation as
opposed to the bang!:

Private sub fraPassFail_AfterUpdate()

if me.fraPassFail = 1 then
Me.Pass = -1
Me.Fail = 0

Else

Me.Pass = 0
Me.Fail = -1

End If

end sub
Problem 2:

Don't have time to make a suggestion, now, sorry.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

On second thought, as Rog hints, it's just plain stupid to have a Pass
and a Fail field for data. Have the one field, Pass and use, in the
option group example:

Private sub fraPassFail_AfterUpdate()

if me.fraPassFail = 1 then me.pass = -1 else me.pass = 0

end sub

While your users will want two choices on their form, if you have this
in your structure, then you might as well just stick with spreadsheets. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[chrisc]

Ok, problem one is now done and dusted, and is working absolutley
brilliantly - many thanks to all for that one :D

Still need to sort my problem with the adding records automatically.

Last night I got it to add the records depending on the amount entered
into the field 'autonum' however it wasnt linking back to the master
records, it was just entering new lines into the faults table, which is
supposed to link to products and then back to batches..

Then I got to another stage where the error was actually telling me
that I couldnt enter records as there was no corresponding record in
the products table...

So I'm in limbo again at this point in time, and if anyones had to do
something like this before, or knows how to I would be forever
greatful!

Cheers,

Chris.

[Rog]

Chris,

Does your main form have products as its data source? If so, you will
have to save a newly entered record first before running the code that
creates fault records. Just add the following line:
RunCommand acCmdSaveRecord
before running the procedure that creates your subform records.

On the other hand, if products is not your data source, you can create
your product record by code, similar to the code I gave you to create
the fault records (except that you don't need the for...next loop).
Something like:
set myset2 = mydb.OpenRecordset("Products"*,dbOpenDynaset)
myset2.AddNew
myset2("somefield") = something
myset2("someotherfield") = somethingelse
myset2.Update
myset2.Close
set myset2=nothing

[chrisc]

Hi Rog,

The tables are:

Batch Detail where 'Batch_ID' is the Primary Key, and an Autonumber.
This is linked to a table called 'Products' where 'Product_ID' is the
Primary Key, which in turn is linked to the Faults table with Primay
Key of Fault_ID.

The main form incorporates both the Batch and Product Table. Users
Select their name which generates the new Batch_ID number, choose a
product they are testing which Generates the Product_ID and then enter
the faults on the subform. The subform knows how to link the faults by
using the Product_ID against each fault logged..

Does that make sence?

I'm going to have a go with that code for saving and see if I can get
that going...

As always, much appreciated.

Chris.

[chrisc]

Right, so this is my code at the moment, on the button which is on the
Products/main form:

Private Sub Command38_Click()
RunCommand acCmdSaveRecord
Dim mydb As Database, myset As Recordset, x As Long
Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("Faults", dbOpenDynaset)
For x = 1 To Me!AutoNum
myset.AddNew
myset("SerialNumber") = x
myset.Update
Next
myset.Close
Set myset = Nothing
Set mydb = Nothing
End Sub

It falls down on this line: Set myset = mydb.OpenRecordset("Faults",
dbOpenDynaset)

With a type mismatch error, and when I hover over it it says
myset=nothing..

Now I did get around this yesterday, but cant for the life of me
remember how, I need to hit the books I think!

Chris.

[chrisc]

Ok, that was something to do with ADO references, so thats fixed and
now im back to this error:

Run-time error '3201':

You cannot add or change a record because a related record is required
in table 'Products'.

Do I need to code in somewhere that the primary key for faults = a
value in the products table, or would it do that of its own accord? Or
am I way of piste...

Cheers,

Chris.

[chrisc]

Woohoo,

Got it working, I'm forever happy! Thanks to everyone that helped,
heres the code that worked if you're interested, its all yours really
Rog!

Private Sub Command38_Click()
RunCommand acCmdSaveRecord
Dim mydb As Database, myset As Recordset, x As Long
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myset = mydb.OpenRecordset("Faults", dbOpenDynaset)
For x = 1 To Me!AutoNum
myset.AddNew
myset("Serial Number") = x
myset("Fault_ID") = Me.Product_List_ID
RunCommand acCmdSaveRecord
myset.Update
Next x
Me.Recalc
myset.Close
Set myset = Nothing
Set mydb = Nothing
End Sub

Cheers,

Chris.