expresions returning field names in vba

C

CristianTCDM

I want to refer to a recordset field like this :
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA) ' strmed = "med"&IntA
.Update
End With
Next IntA
Everything seems to be OK except the ![Eval(strmed)] part .
If somebody can help it would be very appreciated
 
J

Jeff Boyce

It appears you are trying to use fieldnames that look like "med1", "med2",
....

If so, you have ... a spreadsheet! Repeating fieldnames (like the above)
are a strong indicator that your table structure would benefit from further
normalization. You won't get the best use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

Or have I read too much into a small snippet of code? If so, provide a more
detailed description for more detailed suggestions...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
C

CristianTCDM

Thanks Jeff

I want to add a new record in Fields [med1],[med2], ...[med7] of MemoR table
with values from field [medlat] of table RpInstant so that first value is in
[med1] , second value is in [med2] and so on ; here is the whole code I tried
:

Private Sub Command36_Click()
Dim dbs As DAO.Database
Dim rstInstant As DAO.Recordset
Dim rstMemoR As DAO.Recordset
Dim strmed As String
Dim varInst As Variant
Dim instROW As Integer
Dim instCOL As Integer
Dim IntX As Integer
Dim IntA As Integer


Set dbs = CurrentDb
Set rstInstant = dbs.OpenRecordset("SELECT RpInstant.NrCrtMED,
RpInstant.NrCrtMR, RpInstant.NewNR, RpInstant.medlat, RpInstant.medfab,
RpInstant.doza, RpInstant.cant, RpInstant.proc, RpInstant.lista,
RpInstant.tipdg, RpInstant.cod FROM RpInstant ORDER BY RpInstant.lista",
dbOpenSnapshot)
Set rstMemoR = dbs.OpenRecordset("MemoRetete")
strmed = "med" & IntA + 1
IntX = rstInstant.RecordCount

varInst = rstInstant.GetRows(IntX)
instROW = UBound(varInst, 2) + 1
instCOL = UBound(varInst, 2) + 1
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA)
.Update
End With
Next IntA

Set dbs = Nothing
Set rstInstant = Nothing
Set rstMemoR = Nothing
End Sub

(I have an application in Access 2007)

Your suggestions are very appreciated ;

Jeff Boyce said:
It appears you are trying to use fieldnames that look like "med1", "med2",
....

If so, you have ... a spreadsheet! Repeating fieldnames (like the above)
are a strong indicator that your table structure would benefit from further
normalization. You won't get the best use of Access' relationally-oriented
features/functions if you feed it 'sheet data.

Or have I read too much into a small snippet of code? If so, provide a more
detailed description for more detailed suggestions...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


CristianTCDM said:
I want to refer to a recordset field like this :
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA) ' strmed = "med"&IntA
.Update
End With
Next IntA
Everything seems to be OK except the ![Eval(strmed)] part .
If somebody can help it would be very appreciated
Click to expand...
Click to expand...
 
D

Douglas J. Steele

While I agree with Jeff that having fields named like that is almost always
a sign of a incorrect database design, try

With rstMemoR
.AddNew
For IntA = 1 To IntX
strmed = "med" & IntA
.Fields(strmed) = varInst(3, IntA)
Next IntA
.Update
End With


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


CristianTCDM said:
Thanks Jeff

I want to add a new record in Fields [med1],[med2], ...[med7] of MemoR
table
with values from field [medlat] of table RpInstant so that first value is
in
[med1] , second value is in [med2] and so on ; here is the whole code I
tried
:

Private Sub Command36_Click()
Dim dbs As DAO.Database
Dim rstInstant As DAO.Recordset
Dim rstMemoR As DAO.Recordset
Dim strmed As String
Dim varInst As Variant
Dim instROW As Integer
Dim instCOL As Integer
Dim IntX As Integer
Dim IntA As Integer


Set dbs = CurrentDb
Set rstInstant = dbs.OpenRecordset("SELECT RpInstant.NrCrtMED,
RpInstant.NrCrtMR, RpInstant.NewNR, RpInstant.medlat, RpInstant.medfab,
RpInstant.doza, RpInstant.cant, RpInstant.proc, RpInstant.lista,
RpInstant.tipdg, RpInstant.cod FROM RpInstant ORDER BY RpInstant.lista",
dbOpenSnapshot)
Set rstMemoR = dbs.OpenRecordset("MemoRetete")
strmed = "med" & IntA + 1
IntX = rstInstant.RecordCount

varInst = rstInstant.GetRows(IntX)
instROW = UBound(varInst, 2) + 1
instCOL = UBound(varInst, 2) + 1
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA)
.Update
End With
Next IntA

Set dbs = Nothing
Set rstInstant = Nothing
Set rstMemoR = Nothing
End Sub

(I have an application in Access 2007)

Your suggestions are very appreciated ;

Jeff Boyce said:
It appears you are trying to use fieldnames that look like "med1",
"med2",
....

If so, you have ... a spreadsheet! Repeating fieldnames (like the above)
are a strong indicator that your table structure would benefit from
further
normalization. You won't get the best use of Access'
relationally-oriented
features/functions if you feed it 'sheet data.

Or have I read too much into a small snippet of code? If so, provide a
more
detailed description for more detailed suggestions...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


CristianTCDM said:
I want to refer to a recordset field like this :
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA) ' strmed = "med"&IntA
.Update
End With
Next IntA
Everything seems to be OK except the ![Eval(strmed)] part .
If somebody can help it would be very appreciated
Click to expand...
Click to expand...
Click to expand...
 
C

CristianTCDM

Thank you, Douglas

I tried your sugestion , but it doesn't work . Now appear mesage : subscript
out of range ; It is because of wrong field naming ?; Is it something I can
do , because for the moment I cannot rename the fields ?

Douglas J. Steele said:
While I agree with Jeff that having fields named like that is almost always
a sign of a incorrect database design, try

With rstMemoR
.AddNew
For IntA = 1 To IntX
strmed = "med" & IntA
.Fields(strmed) = varInst(3, IntA)
Next IntA
.Update
End With


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


CristianTCDM said:
Thanks Jeff

I want to add a new record in Fields [med1],[med2], ...[med7] of MemoR
table
with values from field [medlat] of table RpInstant so that first value is
in
[med1] , second value is in [med2] and so on ; here is the whole code I
tried
:

Private Sub Command36_Click()
Dim dbs As DAO.Database
Dim rstInstant As DAO.Recordset
Dim rstMemoR As DAO.Recordset
Dim strmed As String
Dim varInst As Variant
Dim instROW As Integer
Dim instCOL As Integer
Dim IntX As Integer
Dim IntA As Integer


Set dbs = CurrentDb
Set rstInstant = dbs.OpenRecordset("SELECT RpInstant.NrCrtMED,
RpInstant.NrCrtMR, RpInstant.NewNR, RpInstant.medlat, RpInstant.medfab,
RpInstant.doza, RpInstant.cant, RpInstant.proc, RpInstant.lista,
RpInstant.tipdg, RpInstant.cod FROM RpInstant ORDER BY RpInstant.lista",
dbOpenSnapshot)
Set rstMemoR = dbs.OpenRecordset("MemoRetete")
strmed = "med" & IntA + 1
IntX = rstInstant.RecordCount

varInst = rstInstant.GetRows(IntX)
instROW = UBound(varInst, 2) + 1
instCOL = UBound(varInst, 2) + 1
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA)
.Update
End With
Next IntA

Set dbs = Nothing
Set rstInstant = Nothing
Set rstMemoR = Nothing
End Sub

(I have an application in Access 2007)

Your suggestions are very appreciated ;

Jeff Boyce said:
It appears you are trying to use fieldnames that look like "med1",
"med2",
....

If so, you have ... a spreadsheet! Repeating fieldnames (like the above)
are a strong indicator that your table structure would benefit from
further
normalization. You won't get the best use of Access'
relationally-oriented
features/functions if you feed it 'sheet data.

Or have I read too much into a small snippet of code? If so, provide a
more
detailed description for more detailed suggestions...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I want to refer to a recordset field like this :
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA) ' strmed =
"med"&IntA
.Update
End With
Next IntA
Everything seems to be OK except the ![Eval(strmed)] part .
If somebody can help it would be very appreciated
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Since varInst is defined using the GetRows method, its subscript is going to
go from 0 to (RecordCount - 1).

That implies you need

..Fields(strmed) = varInst(3, IntA - 1)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


CristianTCDM said:
Thank you, Douglas

I tried your sugestion , but it doesn't work . Now appear mesage :
subscript
out of range ; It is because of wrong field naming ?; Is it something I
can
do , because for the moment I cannot rename the fields ?

Douglas J. Steele said:
While I agree with Jeff that having fields named like that is almost
always
a sign of a incorrect database design, try

With rstMemoR
.AddNew
For IntA = 1 To IntX
strmed = "med" & IntA
.Fields(strmed) = varInst(3, IntA)
Next IntA
.Update
End With


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


CristianTCDM said:
Thanks Jeff

I want to add a new record in Fields [med1],[med2], ...[med7] of MemoR
table
with values from field [medlat] of table RpInstant so that first value
is
in
[med1] , second value is in [med2] and so on ; here is the whole code I
tried
:

Private Sub Command36_Click()
Dim dbs As DAO.Database
Dim rstInstant As DAO.Recordset
Dim rstMemoR As DAO.Recordset
Dim strmed As String
Dim varInst As Variant
Dim instROW As Integer
Dim instCOL As Integer
Dim IntX As Integer
Dim IntA As Integer


Set dbs = CurrentDb
Set rstInstant = dbs.OpenRecordset("SELECT RpInstant.NrCrtMED,
RpInstant.NrCrtMR, RpInstant.NewNR, RpInstant.medlat, RpInstant.medfab,
RpInstant.doza, RpInstant.cant, RpInstant.proc, RpInstant.lista,
RpInstant.tipdg, RpInstant.cod FROM RpInstant ORDER BY
RpInstant.lista",
dbOpenSnapshot)
Set rstMemoR = dbs.OpenRecordset("MemoRetete")
strmed = "med" & IntA + 1
IntX = rstInstant.RecordCount

varInst = rstInstant.GetRows(IntX)
instROW = UBound(varInst, 2) + 1
instCOL = UBound(varInst, 2) + 1
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA)
.Update
End With
Next IntA

Set dbs = Nothing
Set rstInstant = Nothing
Set rstMemoR = Nothing
End Sub

(I have an application in Access 2007)

Your suggestions are very appreciated ;

:

It appears you are trying to use fieldnames that look like "med1",
"med2",
....

If so, you have ... a spreadsheet! Repeating fieldnames (like the
above)
are a strong indicator that your table structure would benefit from
further
normalization. You won't get the best use of Access'
relationally-oriented
features/functions if you feed it 'sheet data.

Or have I read too much into a small snippet of code? If so, provide
a
more
detailed description for more detailed suggestions...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


message
I want to refer to a recordset field like this :
For IntA = 1 To IntX
With rstMemoR
.AddNew
![Eval(strmed)] = varInst(3, IntA) ' strmed =
"med"&IntA
.Update
End With
Next IntA
Everything seems to be OK except the ![Eval(strmed)] part .
If somebody can help it would be very appreciated
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field Reference Problem 4
VBA not updating recordsets (tables) 1
use query Criteria from combo box to set subform Filter 1
Update Recordset with VBA 4
Field names in For loop array?? 4
SQL Select in VBA to Open Recordset 2
extract items from list in single field, revisited 4
Refer to LINQ Field name as Variable 1

Top