go to a field in a form

[Guest]

I have vials that are kept in boxes. In the table vials, every vial is a
record. for every vial there is a field with the number of the box, another
for the column(represented by letters) and another for the row(1, 2, 3) and
the key is the vialID. I want to have a table that represents the boxes. Each
record will be a box, and the fileds will be the positions in the box: a1,
a2, a3, a4, b1, b2, b3. since each box has 10 columns and 10 rows, every
record has 100 fields. If vial 223 is in box 2, column a, row 2, I want to go
to the form boxes, record number 2, field a2 and write there 223. I managed
to use the goto command in a macro to go to the field a2 and the filed get
focus. but I don't know how to write 223 automatically (so the user don't
have to write it. I would appreciate some help, without the need to write in
visual basic, since I don't know it. Many thanks

 

[Guest]

You could use the "setvalue" macro - place it on the line "below" the GoTo
line on the same macro

Or - creat a form based on your Vial Table and insert a "find Combo"
[FinVial]. Set the small bit of code to Go To the Vial you select in the
combo drop down and then set the text in the [VialName] to whatever you want.
Set the code to run on the AfterUpdate option (and place me.FindVial = "" )
to clear the combo ready for the next search.

Try something like this - "I have not had time to check" this but if you
want to go ahead with the form let me know and I will look at it.

Private Sub FindVial_AfterUpdate()
On Error GoTo VialName_AfterUpdate_Err

DoCmd.GoToControl "VialName"
Forms!frmFormName!VialName = "WhatEver"

CDStreet_AfterUpdate_Exit:
Exit Sub

End Sub

 

[John Vinson]

I have vials that are kept in boxes. In the table vials, every vial is a
record. for every vial there is a field with the number of the box, another
for the column(represented by letters) and another for the row(1, 2, 3) and
the key is the vialID. I want to have a table that represents the boxes. Each
record will be a box, and the fileds will be the positions in the box: a1,
a2, a3, a4, b1, b2, b3. since each box has 10 columns and 10 rows, every
record has 100 fields. If vial 223 is in box 2, column a, row 2, I want to go
to the form boxes, record number 2, field a2 and write there 223. I managed
to use the goto command in a macro to go to the field a2 and the filed get
focus. but I don't know how to write 223 automatically (so the user don't
have to write it. I would appreciate some help, without the need to write in
visual basic, since I don't know it. Many thanks

I'd REALLY suggest that you reconsider this table design. Storing data
- the vial position - in a fieldname is simply BAD DESIGN. Suppose you
go to new boxes with 144 positions? What do you do - redesign your
table, all your queries, all your forms?

I'd suggest two separate fields for the row and the column. Three
fields together - BoxID, RowNum, ColNum - will together uniquely find
a vial. Each vial should be in its own record in a table related
one-to-many to the box table.

John W. Vinson[MVP]