save new value connected with one parameter

[Smoki]

Hello,
I have form with combo box and text fields. When I choose value in combo box
(I choose serial number for equipment), other text fields have been filled
(in tb_POPIS I go to specific row by choosing value from combo box, but field
Location is in tb_DKP, other text fields are in tb_POPIS; these two tables
are in relationship DKP-POPIS one-to-many). Also, I have another combo box on
my form, for location. Now I want to change location of equipment. And I read
this combo box from second table-tb_DKP.
And, on exit from form, I want that my equipment has new location-which I
choose in combo box. How to do this?
Any idea?!

Thanks, Smoki

 

[Daryl S]

Smoki -

If your location combobox is bound to the location field in the table, then
it will update that record if you change the selection in the combobox. You
don't need two controls for the location.

 

[Smoki]

I know what you say. But, in my tbl_POPIS I have in column Location not the
real name of places, then Location_ID (it's numbers) from tbl_DKP. And these
two tables are in relationship one-to-many. So, on my form text field for
Location is bound to Location from tbl_DKP, not from tbl_POPIS.
Combo box for choosing new location read also from tbl_DKP. So, I need to
read names of locations in DKP, and to change it in POPIS, but as you see, I
need to change Location_Id.
I hope I explain these clearly?! Sorry if I didn't, if you want I can write
longer, and to try deeply to explain...
Thanks for help,
Smoki

 

[Daryl S]

Smoki -

You still only need one control (the Combo box) for Location. The
recordsource for the combo box is tbl_DKP, which has the location_ID and it's
description. The combo box would contain both fields, but the location_ID
would normally be set to 0" width so it does not display in the combo box.
The combo box is still bound to the tbl_POPIS location field, and the bound
column would be the location_ID column (even if it is not visible to the end
users).

 

[Smoki]

I think I didn't understand

Smoki -

You still only need one control (the Combo box) for Location. The
recordsource for the combo box is tbl_DKP, which has the location_ID and it's
description. - How did you mean to set recordsource for Combo box?

The combo box would contain both fields, but the location_ID
would normally be set to 0" width so it does not display in the combo box. - I understand this. And I did this with my second combo box, where I choose new location for equipment.

The combo box is still bound to the tbl_POPIS location field, and the bound
column would be the location_ID column (even if it is not visible to the end
users). - I don't understand this part, I put recordsource for my form to tbl_POPIS. And for text field just to read it from tbl_POPIS. But, like I said, secongd combo box for new location is bound to tbl_DKP, to read from it.

And if I leave only one combo box, how to change location, because on scroll
down, it doesn't show anything

Smoki

 

[Daryl S]

Smoki -

The combo box on your form for Location is based on the tbl_DKP table. This
table has both the location_ID and the description of the location. Check
the recordsource property of the combo box. It may be just the table
(tbl_DKP) or a query based on the table. In either case, the recordsource
should show contain both the location_ID and the description of the location.


The combo box should have Format properties like this:
column Count 2
Column Widths 0",2" (the second value can be anything, the first would be 0"
so the ID is not shown

This same combo box should have Data properties like this:
Control Source Location_ID (the field name in the tbl_POPIS table to hold
the location id)
Row Source tbl_DKP (or a query based on it)
Bound Column 1

This way, the user sees the name of the location in the drop-box, but when
they select it, the ID goes into the tbl_POPIS table.

Does that help?

 

[Smoki]

I think it would help, but I made stupide begginers mistake
I gave the same name - Location, to second row in tbl_DKP, where is
description, and to row in tbl_POPIS, which is in relationship with DKP_Id.
So, I done like you said, but I think when I put Location for Control
Source, db changes Location in tbl_DKP, not tbl_POPIS.
I wanted to change the name of this row in tbl_POPIS, but I can't, because
of relationship.
I know, it's my mistake, but do you have any idea about this?!

Smoki

 

[Smoki]

Me again, I didn't choose corect Location field, but now I put it from
tbl_POPIS. but now, I got message:

Run-time error 3079
The specified field [Location] could refer to more than one table listed in
the FROM clause of your SQL statement. And my SQL looks like this:

SELECT POPIS.Serijski_broj, DKP.Lokacija, POPIS.Lokacija, POPIS.Soba,
POPIS.Opis, POPIS.Tip_opreme, Broj.Inventarski_broj, POPIS.Verzija_IOS,
POPIS.Datum, POPIS.U_upotrebi, POPIS.Predlog_za_rashod, POPIS.Rashodovan,
POPIS.Otudjen
FROM DKP INNER JOIN (Broj INNER JOIN POPIS ON Broj.Broj_ID = POPIS.Broj_ID)
ON DKP.DKP_ID = POPIS.Lokacija
WHERE (((POPIS.Serijski_broj)=[Forms]![TEST izmena po SER
BROJU]![Serijski_broj]))
ORDER BY POPIS.Serijski_broj DESC;

Smoki

 

[Daryl S]

Smoki -

The SQL you posted is fully qualified, so I don't think that is the one with
the error. Check the other queries (maybe one of the RecordSources) to find
it. Or do a search in the code on Location. From the error message, it will
be in the SELECT portion of a SQL statement, and it will just need the
tablename prepended to the fieldname (e.g. change Location to POPIS.Location,
or whatever table is appropriate for that SQL).

--
Daryl S

Me again, I didn't choose corect Location field, but now I put it from
tbl_POPIS. but now, I got message:

Run-time error 3079
The specified field [Location] could refer to more than one table listed in
the FROM clause of your SQL statement. And my SQL looks like this:

SELECT POPIS.Serijski_broj, DKP.Lokacija, POPIS.Lokacija, POPIS.Soba,
POPIS.Opis, POPIS.Tip_opreme, Broj.Inventarski_broj, POPIS.Verzija_IOS,
POPIS.Datum, POPIS.U_upotrebi, POPIS.Predlog_za_rashod, POPIS.Rashodovan,
POPIS.Otudjen
FROM DKP INNER JOIN (Broj INNER JOIN POPIS ON Broj.Broj_ID = POPIS.Broj_ID)
ON DKP.DKP_ID = POPIS.Lokacija
WHERE (((POPIS.Serijski_broj)=[Forms]![TEST izmena po SER
BROJU]![Serijski_broj]))
ORDER BY POPIS.Serijski_broj DESC;

Smoki

Smoki -

The combo box on your form for Location is based on the tbl_DKP table. This
table has both the location_ID and the description of the location. Check
the recordsource property of the combo box. It may be just the table
(tbl_DKP) or a query based on the table. In either case, the recordsource
should show contain both the location_ID and the description of the location.


The combo box should have Format properties like this:
column Count 2
Column Widths 0",2" (the second value can be anything, the first would be 0"
so the ID is not shown

This same combo box should have Data properties like this:
Control Source Location_ID (the field name in the tbl_POPIS table to hold
the location id)
Row Source tbl_DKP (or a query based on it)
Bound Column 1

This way, the user sees the name of the location in the drop-box, but when
they select it, the ID goes into the tbl_POPIS table.

Does that help?