Code Help

  • Thread starter Thread starter Patti
  • Start date Start date
P

Patti

When I run the following code, I get a compile error "Invalid use of New
Keyword" and in the statement Set rs = New DAO.Recordset, the " New
DAO.Recordset" portion is highlighted. Can anyone tell me why?

I am testing this in 2002, but it needs to be compatible with '97. I do
have a DAO listed in References.


Private Sub CmdWriteToTable_Click()
Dim rs As DAO.Recordset
Dim i As Long
'Check if the entries are valid
If Not (IsNumeric(txtFirstSeqNum) And IsNumeric(txtLastSeqNum)) Then Exit
Sub
If txtFromNumber > txtToNumber Then Exit Sub

'Add the records to the table
Set rs = New DAO.Recordset
rs.Open "SequenceNumbersAssigned", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
For i = txtFirstSeqNum To txtLastSeqNum
rs.AddNew
rs!DateAssigned = txtDateAssigned ' (that is: rs!TableFieldName)
rs!Code = txtCode
rs!SeqNum = i
rs!Quant = txtQuant
rs.Update
Next i
rs.Close
Set rs = Nothing
End Sub
 
Dear Patti:

I am absolutely not an expert, but try removing the word "New" from your
line:

Set rs = New DAO.Recordset
Set rs=DAO.Recordset

This might be enough...

HTH
Fred Boer
 
Set rs = New DAO.Recordset
rs.Open "SequenceNumbersAssigned", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
Click to expand...

You need to combine these.

Set rs = CurrentDb.OpenRecordset("SequenceNumbersAssigned", dbOpenDynamic,,
dbOptimistic)

It appears that you may be mixing DAO and ADO syntax.
 
Thanks Wayne,

Now I get run-time error 3001: invalid argument & "Set rs =
CurrentDb.OpenRecordset("SequenceNumbersAssigned", dbOpenDynamic,,
dbOptimistic)" is highlighted. Am I missing something else?

(I probably am mixing DAO & ADO, but unfortunately, I wouldn't know the
difference. The important thing is this code must work in '97)

Regards,

Patti
 
Ok, I took what you had and found the closest thing. I suspect that all you
actually need is

Set rs = CurrentDb.OpenRecordset("SequenceNumbersAssigned", dbOpenDynaset)

The dbOpenDynamic is for ODBC Direct workspaces.
 
You're right Wayne - it works now.

Just one more question if you don't mind. After the information is written
to the table, the form text boxes are still populated. What is the best
way to clear them so that they'll be ready to accept the next batch of
information?

Thanks again,

Patti
 
I take it that this is an unbound form? Is there a reason you don't just
apply the record source to the form and bind the controls to it?

You could loop through the controls and set them to "defaults". You can
either just list each control or try something like:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl = Null
End If
Next

Include the other types of controls you want to reset. Here is a list of the
constants.

acBoundObjectFrame
acCheckBox
acComboBox
acCommandButton
acCustomControl
acImage
acLabel
acLine
acListBox
acObjectFrame
acOptionButton
acOptionGroup
acPage
acPageBreak
acRectangle
acSubform
acTabCtl
acTextBox
acToggleButton

Of course, not all of these will have a value and some, such as a
multiselect listbox, will require special handling. Instead of the If
statement, a Select Case statement would probably be easier, listing several
control types on one line.
 
Yes, it is an unbound form. It is writing to a table that has 4 fields:
DateAssigned, Code,SeqNum, & Quantity. I could base the form on the table,
but I wanted to give the person entering the data the option for batch
entries. For example, the first sequence number is 1300, the last is 1350,
and they all have the same date and quantity. So this method is saving a
lot of keystrokes. Maybe it is possible to do that with a bound form, but
I'm not aware of how.

The code you helped adapt is working perfectly. I appreciate the
assistance!

Regards,

Patti
 
You could set up dynamic default values for the controls if the form is a
bound form. In the form's BeforeUpdate event, set the default value of the
controls to their current value and that will give you the stable date you
mention automatically. For your sequence number, you could set the default
value to the current value +1.
 
Back
Top