trouble with OpenRecordset

[inlanoche]

I am trying to work out a process in which a value from the control source in a form gets put into another table. I can go into the details if needed, but for now, here is my problem.

here is the bit of code (I have commented out all but the OpenRecordset stuff):

Private Sub CompleteBoxNo_DblClick(Cancel As Integer)
    Dim aDB As Database
    Dim aRS As Recordset
    Dim paramA As String
    
    Dim aRoom As String
    Dim aBox As String
      
    aRoom = Me.OfficeNumber.Value
    aBox = Me.CompleteBoxNo.Value
    paramA = Forms!aMainMenu.ThisLocCode.Value
    Debug.Print paramA
    Debug.Print aBox
    
    
    'open the query where the data will be changed
    Set aDB = CurrentDb
    'Set aRS = aDB.OpenRecordset("dbo_BoxNoQuery", dbOpenDynaset)
    Set aRS = aDB.OpenRecordset(BoxNumbers)
                          
    'Set aRS = aDB.OpenRecordset( _
    '"SELECT * FROM dbo_BoxNumbers WHERE LocCode = " & _
    'Forms!aMainMenu.ThisLocCode, dbOpenDynaset, dbSeeChanges)
    
    'aRS.FindFirst "BoxNumber" = aBox
    
    'If aRS.NoMatch Then
    '    MsgBox "Error", vbCritical, "Error"
    'Else
    '    MsgBox "Good!", vbOKOnly, "Worked"
    '    Debug.Print aRS.Fields("BoxNumber")
    'End If
    
    'Make sure to close the recordset
    aRS.Close
    
End Sub

As you can see I have tried several ways to get this going. The recordset I would like to open and edit is the query dbo_BoxNoQuery. This query is based off a table linked from an SQL database, and has a parameter for LocCode (as you can see with my SELECT statement) which is pulled from another active form (this works as I can see in the debug check)

my last and still active check is to try and open the old BoxNumbers table that is local to this database, and it too fails with error 3078 (can't find the table).:banghead:

Maybe I am going about this wrong, and any help to point me in the right direction would be greatly appriciated.

 

[rzw0wr]

Looking at you code.
Is the data you want to enter into another table on the form?
This may take several questions.
Dale

 

[inlanoche]

I am working with 2 tables (one the control source of the form, the other queried)

Before you ask, no I cannot put them together on a query as they share a common field (the room number).

The form show the information for the currently selected room. I have made a list box that lists all the network jack values. there is also another list box that shows the jacks associated with the current room. because of the varying amount of jacks per room (average of 1-6, but can have in excess of 12) I look up the jack table and show only the values tyhat match the room number.

Hope that does not sound too complicated.

the statement I would like to use is this:

set aRS = aDB.OpenRecordset("dbo_BoxNoQuery")

the issues I think that are coming up are that :

1. the data from this query comes from a linked table from an ODBC source (SQL)

2. the query takes a parameter from another (active) form to streamline the data (LocCode = Forms!aMainMenu.ThisLocCode) to only show the listing for the current office.

once I have the record set I will find the correct record, then add the room number (aRoom var) to the field OfficeNumber in the recordset... if I can get that far...

Again, I may be going about this the wrong way, but this is the first time I have tried something like this, and don't really know how to explain it.

Thanks for the help.

 

[rzw0wr]

Thanks for the information.
From where are you getting the data you want to store?
I what table do you want to store it.
What is the name of the data field you want to store.

Dale

 

[jdraw]

Can you restate in a few lines exactly WHAT you are trying to do? Keep it in plain English, as if you were talking to a 10 yr old -- no access, no comboboxes etc.

You have some sort of code as a solution (HOW) to a problem we do not understand. We need to focus on WHAT you want, look at some options and then proceed with HOW.

Good luck.

 

[inlanoche]

Thanks for the information.
From where are you getting the data you want to store?
I what table do you want to store it.
What is the name of the data field you want to store.

Dale

from the control source (table called dbfficeListings Query) I want to take the value OfficeNumber (on the form it is me.OfficeNumber which I have stored in the var aRoom)

I need to look up the jack that was selected in the listbox of this form, that I have stored in the var aBox

I need to look up aBox in the query dbo_BoxNoQuery. When I find that record I want to put aRoom into the field OfficeNumber of the query.

[EDIT]: an added note here, in the code I listed above, I forgot the "s around the table BoxNumbers. So I have gotten it to "somewhat" work with the local table, but this is an old decrept table, and is not usable. This note is incase someone points that out. I need it to work with the data I am pulling from the SQL server.

 

[rzw0wr]

OK. you can not add data to a query.
However you want to take OfficeNumbe from a table named dbfficeListings Query.
You then want to store this data in dbo_BoxNoQuery
What is the list box name? Is it a list box or a combo box.

Dale

 

[inlanoche]

OK. you can not add data to a query.
However you want to take OfficeNumbe from a table named dbfficeListings Query.
You then want to store this data in dbo_BoxNoQuery
What is the list box name? Is it a list box or a combo box.

Dale

I am not planning to add a record to the query, but I don't think you are right if you are stating you cannot change data in a query. I can go into the query dbo_BoxNoQuery and edit the OfficeNumber field. I am trying to automate that process and it will be long and grueling for one office, let alone all the offices that will be stored in the database.


What I need to do is take a value from the current form (controled by a different table), OfficeNumber, and put it into the record that corresponds with the value selected in the listbox (the list of all the jacks) in the dbo_BoxNoQuery.OfficeNumber field.

 

[rzw0wr]

Maybe this will help you.


http://msdn.microsoft.com/en-us/library/dn125824.aspx


Dale

 

[inlanoche]

I have looked through that (ok not with a fine tooth comb) but I seem to be getting various issues. If I follow what they say there:
Set aRS = aDB.OpenRecordset("dbo_BoxNoQuery", dbOpenDynaset)
I get the run-time error 3061 - Too few parameters. Expected 1

Looking this up I found that it is due to my query having a parameter, so I looked that up and saw someone else doing a select statement instead of calling the query (also instead of making a querydef, which started to confuse me more and I stayed away from that) :
Set aRS = aDB.OpenRecordset( _
"SELECT * FROM dbo_BoxNumbers WHERE LocCode = " & _
Forms!aMainMenu.ThisLocCode.Value) ', dbOpenDynaset, dbSeeChanges)

but if I add the dbOpen and the dbSee I get the same error. If I leave that out, I get 3622 - you must use dbSeeChanges option when accessing a SQL server table that has an IDENTITY column (need an auto number field)

Both of these seem out of the scope of what they are showing on that link, or at least the examples they give. If needed I can give a break down of the 2 tables:

dbfficeListing: (Control source of the form)
AutoNumber - AutoNumber
LocCode - Short Text
OfficeNumber - Short Text
Type - Short Text
Column - Short Text
Floor - Short Text

dbo_BoxNumbers: (the source of the query dbo_BoxNoQuery)
AutoNumber - AutoNumber
LocCode - Short Text
BoxNumber - Short Text
OfficeNumber - Number
SwitchIP - Short Text
SwitchPort - Short Text
Notes - Short Text

 

[rzw0wr]

The short and simple to DAO recordset is
Dim recordset as DAO.Recordset

Set Recordset = CurrentDb.Openrecordset()
Note; Either an SQL, Table or Query
Recordsets have field property Field()
You can use VBA code with a Recordset.
You can open more than 1 recordset but they MUST be named differently.
IF you are going to do more than look at the data you must use either AddNew or Edit.
To save a recordset:
Recordset.Update
Recordset.BookMark=LastModified
To move in a recordset,
.Movefirst
.MoveNext
.MovePrevious
.MoveLast
You need to close a recordset.
Recordset.close
Set recordset=nothing

Dale

 

[rzw0wr]

inlanoche, by now we are pretty much aware of what you want to do.
What we need now is for you to give us the information we need to do this.
The code you are using is faulty.
All we need is for you to answer our questions, without giving directions and comments.
If you will do this then we can work with you to solve this problem quickly.

Thank you,
Dale

 

[inlanoche]

Sorry Dale, not trying to be snooty, and I am greatful for the help. Bad day at work all round. It is frustrating knowing what you want to do, knowing it is possible, but not understanding the syntax.

I know I need to take baby steps before leaping in, it's just that the information I have found varies, and doesn't take what I am implementing directly into context...

ok, I will type both the database and the recordset as DAO and give that a shot. I know my code is faulty (and at this point quite messy), and that is also why I am here.

Is there anything else you need to see/know? I'll be back a bit later to let you know how it went. I will start with new code and post again later.

[EDIT]
Ok going back to the bare bones:

Private Sub CompleteBoxNo_DblClick(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim aRoom As String
    Dim aBox As String
      
    aRoom = Me.OfficeNumber.Value
    aBox = Me.CompleteBoxNo.Value
    
    
    'open the query where the data will be changed
    Set db = CurrentDb
    Set rs = db.OpenRecordset("dbo_BoxNoQuery", dbOpenDynaset)
                          
    
    'Make sure to close the recordset
    rs.Close
    Set rs = Nothing
    
End Sub

I know this doesn't work, but one step at a time. I get the error that there is too few parameters, expecting 1. From my research this due to the fact that my query (dbo_BoxNoQuery) has a criteria -Like [Forms]![aMainMenu]![ThisLocCode]- and that the record set cannot view the form it is referencing (I don't know the full of it, it just can't).

This leaves me 2 options. first I can make a querydef, second I can use a select statement instead. Am I on the right track so far?

 

[JHB]

You have to enclosure the text criteria by quotes:

Set aRS = aDB.OpenRecordset("SELECT * FROM dbo_BoxNumbers WHERE LocCode = '" & _
Forms!aMainMenu.ThisLocCode.Value & "'", dbOpenDynaset)

 

[inlanoche]

You have to enclosure the text criteria by quotes:

I'm not the best with SQL, thanks. Ok I tried that:

Set rs = aDB.OpenRecordset( _
"SELECT * FROM dbo_BoxNumbers WHERE LocCode = " & _
"'Forms!aMainMenu.ThisLocCode'", dbOpenDynaset, dbSeeChanges)

now I get the error : Object required

 

[JHB]

I'm not the best with SQL, thanks. Ok I tried that:

Set rs = aDB.OpenRecordset( _
"SELECT * FROM dbo_BoxNumbers WHERE LocCode = " & _
"'Forms!aMainMenu.ThisLocCode'", dbOpenDynaset, dbSeeChanges)

now I get the error : Object required

The above code, is not the solution I sent you!!

Set rs = aDB.OpenRecordset("SELECT * FROM dbo_BoxNumbers WHERE LocCode = '" & Forms!aMainMenu.ThisLocCode & "'", dbOpenDynaset, dbSeeChanges)

 

[inlanoche]

Thanks JHB,

I tried the initial code snippit that you posted, but I think I erred a bit with the code I posted, and it didn't work. So I tried to see if maybe something was typed up bad (from me messing with what you posted)...

I think it was the form data. aMainMenu.ThisLocCode.Value, this gave the same error, but without .Value, the code passes. Now I'm going to do the find to see if I can see the right record. I think this has got me going.

Thanks to all that assisted with this, and again, I appologize for my impatence on friday

One thing that just came to me... would I still be able to edit the data in the recordset that is created this way? That is the whole point of this process... In anycase, will try...

[EDIT]:

Ok, looks like this works great, but my search not so great. This line will work:

rs.FindFirst "BoxNumber = 308-PD001"

308-PD001 is the first record. if I click on this in the listbox, this umber gets stored in the string var aBox (aBox = Me.CompleteBoxNo.Value), which I have tested with a debug print.

the problem is, I can't get the var to work in the above code. I have tried these:

rs.FindFirst "BoxNumber = " & aBox

(error 3070 - doesn't rec. PD001 as a vaild fieldname or expression)

rs.FindFirst "BoxNumber = aBox"

(error same as above but with aBox not recognized)

rs.FindFirst "BoxNumber = 'aBox'"

(this runs, but it doesn't find it. I assume it's because it's looking for aBox as aposed to the value in aBox)


I'm having trouble (again) with the syntax here....:banghead:

 

[inlanoche]

Ok, I found it... :

rs.FindFirst "[BoxNumber] = '" & aBox & "'"

The quotes thing is killing me...

OK! the whole thing works great now, and again, I thank all that have helped out. Here is the completed code incase anyone else is doing something like this and needs a sample (though I won't say great sample, just a sample):

Private Sub CompleteBoxNo_DblClick(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    Dim aRoom As String
    Dim aBox As String
 
    aRoom = Me.OfficeNumber.Value
    aBox = Me.CompleteBoxNo.Value
 
    'open the query where the data will be changed
    Set db = CurrentDb
    'Use SQL statement to get the recordset, as we need to use a paramater pulled
    'from another form.
    Set rs = db.OpenRecordset("SELECT * FROM dbo_BoxNumbers WHERE LocCode = '" & _
    Forms!aMainMenu.ThisLocCode.Value & "'", dbOpenDynaset, dbSeeChanges)
 
    'force recordset to view last record to ensure all of it is loaded
    rs.MoveLast
    rs.MoveFirst
 
    'find the record of the item click on the listbox
    rs.FindFirst "[BoxNumber] = '" & aBox & "'"
 
    If rs.NoMatch Then
        MsgBox "Error - " & aBox & " Not Found", vbCritical, "Error"
    Else
        'MsgBox "Good!", vbOKOnly, "Worked"
        'found the record, now we can change the OfficeNumber value to match the current room
        rs.Edit
        rs!OfficeNumber = aRoom
        rs.Update
    End If
 
 
    'Make sure to close the recordset
    rs.Close
    Set rs = Nothing
 
    'update the visible data
    Me.TheseBoxNo.Requery
    Me.Refresh
 
End Sub