Solved calling the value of a field from a main form -> subform into a query

hi Florin,

for reference, here is how you said the codm field breaks down:
202012011 is coding for
20 - specialty
2 - year of study
01 - matter order number
2011 - year of study
Click to expand...

BACKUP your database. Always do this before running any update queries. Open database.

1. Go to the design view of materii

2. Change the Data Type of codm to Text or Short Text, Field Size=9 (bottom pane)

3. Add these 4 fields to your table to keep track of the codm parts (OR WHATEVER YOU WANT TO CALL THEM -- KEEP NAMES THE SAME AS THEY'RE CALLED IN OTHER PLACES. DATA TYPE IN OTHER PLACES SHOULD MATCH THESE):
  1. SpecialtyNbr, Number, Field Size = Integer, Default Value is nothing (delete 0 if Access adds it)
  2. StudyYr, Number, Field Size = Integer, Default Value is nothing (delete 0 if Access adds it)
  3. MatterNbr, Number, Field Size = Integer, Default Value is nothing (delete 0 if Access adds it)
  4. Yr, Number, Field Size = Integer, Default Value is nothing (delete 0 if Access adds it)
Make an Update Query to fill the new fields

Create a new query based on the materii table and go to the design view
  • Put codm on the grid in the Field cell
  • Criteria --> Is Not Null
you said,
specialty which can be from 1 to 30 (ex. 9,17,24 ...)
Click to expand...

Add a calculated column:
  • Field --> LenCodm: Len([codm])
Look at the data. Is codm always 8 or 9?
it should be 8 if Specialty number is less than 10, or 9 if not

So far, this is still a Select query to show data from your table. Now go back to the Design View

We don't need the LenCodm field so you can delete it.

Change the query to an Update query. On the Design ribbon, in the Query Type group, click on Update

ribbon_QueryDesign_QueryType_Update.png


the grid will change. Now you have a row called Update To

Add this information to the grid:
Field --> SpecialtyNbr
Update To --> IIf( Len([codm])=8, Left([codm],1), Left([codm],2) )

Field --> StudyYr
Update To --> Mid( [codm], IIf(Len([codm])=8, 2, 3),1)

qUp_materii_inProgress.png


continue this pattern with the new MatterNbr and Yr fields. Even what is being calculated in the UpdateTo cell is text, and the fields we're updating are numbers, Access will implicitly convert the data type and get it right :)

Save this query as --> qUp_materii

To run and make the changes to your table, click the ! Run button on the Design ribbon when you are looking at the design view of the query

~~~~~~~~~~~~~~~~ unrelated:

Change the NAME of the Note table since "note" is a reserved word. You can look up bad names and reserves words here:
allenbrowne.com/AppIssueBadWord.html

Perhaps call it Notes. I also see you have 3 fields in the Notes table that are also reserved words: Name, Type and Data

There might be other reserved words too. I didn't look. On forms and reports, you'll also have to change
  • the RecordSource
  • the Control Source and Name (to match Control Source) properties of controls based on this table.
 
ps, Florin
actually, this:
IIf( Len([codm])=8, Left([codm],1), Left([codm],2) )
is better written as this:
Left([codm], IIf( Len([codm])=8, 1, 2) )

If you add new data with Access too, then we can help you with code you'll need on your forms.

I am curious: how do you get the data that you put into Access? Does it come from another system as a text file? What is the format?
 
strive4peace said:
ps, Florin
actually, this:
IIf( Len([codm])=8, Left([codm],1), Left([codm],2) )
is better written as this:
Left([codm], IIf( Len([codm])=8, 1, 2) )

If you add new data with Access too, then we can help you with code you'll need on your forms.

I am curious: how do you get the data that you put into Access? Does it come from another system as a text file? What is the format?
Click to expand...

Hello strive4peace
thank you very much for all your advice
these are also good solutions

in the meantime I had done something like that
SELECT codm,
Mid ([codm], 1,2) as SPEC,
Mid ([codm], 3,1) AS ANSTD,
Mid ([materials.code], 4.2) AS NRORD,
Right ([codm], 4) AS PROMO
FROM matters
WHERE Mid ([codm], 1,2) = '07' And Right ([codm], 4) = '2011' And Mid ([codm], 3,1) = '2';
CODM - CONVERT TO TEXT
I also tried:
IIf (len (str ([codm])) = 8, "0" & [codm], [codm])
today i will see how i will start

I have an older application made in VFP
I am going to export data - most likely in XLS or CSV
(CSV because I have many records) from DBF
 
VasiuF said:
Hello strive4peace
thank you very much for all your advice
these are also good solutions

in the meantime I had done something like that
SELECT codm,
Mid ([codm], 1,2) as SPEC,
Mid ([codm], 3,1) AS ANSTD,
Mid ([materials.code], 4.2) AS NRORD,
Right ([codm], 4) AS PROMO
FROM matters
WHERE Mid ([codm], 1,2) = '07' And Right ([codm], 4) = '2011' And Mid ([codm], 3,1) = '2';
CODM - CONVERT TO TEXT
I also tried:
IIf (len (str ([codm])) = 8, "0" & [codm], [codm])
today i will see how i will start

I have an older application made in VFP
I am going to export data - most likely in XLS or CSV
(CSV because I have many records) from DBF
Click to expand...


Hi all
I would need the following sequence to write it in vba access:

I have 2 tables: table1 (field1, field1, field1) and table2 (field1, field1, field1)
where table1.field1 = table2.field1

LEAVING A FIELD FORM

select table1
filter by field1 and field2
go to the first recording after the filter
if eof
message .... no recordings found
else
var a = field1
var b = field2
var c = field3
select table2
I'm looking for a record according to criterion tabela1.field1 = tabela2.field1
if not found
message ... there are no recordings with criterion
else
var a = field1
var b = field2
var c = field3
endif
endif

THEN TO CONTINUE ON THE CLICK OF A BUTTON IN THE SAME FORM

select table2
search for field1 = var a
if not found
insert ......
else
update
endif
select table1
next record
IF not eof ()
var a = field1
var b = field2
var c = field3
else
message ... completed
endif

I wrote the code specifically in Visual fox pro and I still don't mess so well with vba access

Thank you
 
hi Florin,

you need to specifically set data type for each variable dimensioned

dim a, b, c as string
-->
dim a as string, b as string, c as string

it is hard to know what you're doing, but here is some code you can look at to get some logic

Rich (BB code): 
dim sMyA as string
sMyA = "whatever you want" '------ set this for the value to find

select table 1
filter by field1 and field2
go to the first record in the filtered table1
-->

'dimension database object variable
dim db as dao.database
'dimension recordset object variables
dim rs1 as dao.recordset
dim rs2 as dao.recordset

'dimension string for SQL statement
dim sSQL as string

' select all fields from table 1 where condition is true
' value is delimited with " since it is a string
sSQL = "Select t1.* FROM [myTable1] as t1 " _
& " WHERE t1.A=""" & sMyA & """"

'set db to current database
set db=currentdb

'open recordset1 to SQL defined by string
set rs1 = db.openrecordset(sSQL)

if rs1.eog then
   msgbox "Record not found in table1",,"exiting"
   goto proc_exit
endif

' select all fields from table 2 where field1 = table1.field1
sSQL = "Select t2.* FROM [myTable2] as t2 " _
& " WHERE t2.A=""" & sMyA & """"
'open recordset2 to SQL defined by string
set rs2 = db.openrecordset(sSQL)

with rs2
   if .eof then
      'record not found -- add
      .Addnew
      'set field A to field A in rs1
      !A= rs1!A
      !fieldnames = value '------ customize value  
      .update
   else
      .edit
      'statements to edit
      .update
   end if
end with

However, I am concerned that you seem to be repeating fields in table1 to table2. Better would be for table1 to have an AutoNumber primary key and table2 to have a corresponding long integer foreign key. Yhen just one field needs to be the same!

----------------------

Please, to help those who look for questions to answer...

  • when you have a new question, start a new thread

  • click on the Solved link above your first post on the right if you've been given enough information to answer your original question, or to realize you need to redesign your database, or you just wish to ask a question a better way instead of continuing the same thread

By the way, very nice drawing of your face! Did you draw it?

thank you!
 
oh! Florin, at the end of your code:

Rich (BB code): 
proc_exit: 'line label

   'release object variables
   if not rs1 is nothing then
      rs1.close
      set rs1=nothing
   end if
   if not rs2 is nothing then
      rs2.close
      set rs2=nothing
   end if
   set db = nothing

   exit sub 'or function
 
VasiuF said:
Hello
come back with details:
pass admin enrollment 111111 and year 3
then you can see what I have so far but it is not ok, because it is not what I wrote to you
anyway you will see that I followed your advice :)
Click to expand...


the picture is made by a small application :)
after the real picture
 
VasiuF said:
Hello
come back with details:
pass admin enrollment 111111 and year 3
then you can see what I have so far but it is not ok, because it is not what I wrote to you
anyway you will see that I followed your advice :)
Click to expand...

strive4peace
did you have time to take a look at the attachment?
 
hi Florin,

now I have the download! Please tell me exactly what to click on and type in. Real fieldnames, not A, B, C -- and what is table1 and table2 -- thank you!

> "the picture is made by a small application "

it is very nice! What is the name of the application? thanks
 
VasiuF said:
ok
tables are "elevi", "materii" and "note"
in the form frmNote_nou must enter "Matricola" -> 111111 enter (here I am looking for a student in the "elevi" table by field ->elevi.matr
then enter (2 or 3) for year study
at this moment I have a selection from the table "materii" for a student
What I want to do is:
to complete each subject (materii) from the filtered ones in detail form and to add / modify the student "note"

to do this now
in frmNote_nou detail if you click on "codm" it will bring you notes introduced for the student in order to modify / add in case there is no note for the selected subject ("materii") and after editing -> click "OK Salvare" to save record then continue with the next subject ("materii") as in the attached picture
but after a few clicks on the subjects, I lose the string and I do not know which subject I went through
and then I thought it would be good to do this in a loop

if you need any further explanations please write to me, I really wish you can help me with this
Click to expand...

at the moment you will see that after entering the study year ( at lost_fosus event)
I will filter "subjects" and go through them in a while but this does not help me, because I have to take each recording separately and edit it
then save it with the "OK Save" button
 
VasiuF said:
thanks for all your help and especially strive4peace for promptness and useful advice
you've given me good things to think about
I plan to restructure my database a bit
and will ask another question when I need more help
Click to expand...

you're welcome, Vasiu ~happy to help
 
You must log in or register to reply here.

Similar threads

D
Solved Running a report based on fields from a main form and a subform
Replies
13
Views
397
Gasman
Gasman
D
My mouse wheel cannot scroll the height of my form when my cursor is atop my continuous subform. Can I always have main form vertical scroll control?
Replies
13
Views
269
diamondDog
D
Musoke
Filtering Subjects in a Subform from a main Form
Replies
3
Views
255
Uncle Gizmo
Uncle Gizmo
L
Resize main form and subform
Replies
5
Views
511
The_Doc_Man
The_Doc_Man
B
Listbox wont search for record
Replies
10
Views
191
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom