Requery Problem

[Tiffini]

I have a problem with two forms I created and I hope that someone can help me solve it. On Form A there is a command button that opens Form B where data is entered. After Form B is closed, Form A needs to be requeried to show the new values. What code do I need to use, and where do I need to put the code?

Thanks,
Tiffini

 

[spacepro]

what is the function of the second form?
Why would you want to open a seperate form to edit the values in form a?

or I have I misread?

Andy

 

[Tiffini]

Well...I am a newbie first off, so I am sure that there are several "better" ways to design the database. However, that was the only way I could figure out how to View and Edit records on one Form and Add new entries from the another form.

 

[WayneRyan]

Tiffini,

In the OnClose event of your second form you can put:

Forms![YourMainForm].ReQuery

Wayne

 

[Tiffini]

Thanks Wayne!!!!

 

[spacepro]

OK.

You can view/edit and add records on the one form, this is the best practice. Just either add a command button and using the wizard create a button that will add new records.

Anyway to requery form a put the following code on the close event of the form B

Forms!FormA.requery

Don't know your situation but one form is best used for data entry/viewing and editing of records.

Hope this helps

Andy

 

[Tiffini]

and

OK! So I tried to use a command button to add a new entry to my Form A, but I have a combo box in the form, so I knew that I would have to add some code to be able to enter a new entry into the combo box. I used the code from "[FAQ] Adding New Entries to Table From a Combobox (ADO)".

Private Sub Combo26_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_ErrorHandler

' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf

' conenction and recordset object variables
Dim cn As Connection
Dim rs As ADODB.Recordset

' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "Contact Info", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("Contact Name") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset connection
End With
Response = acDataErrAdded ' confirm record added
Else
Me.Combo26.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If

Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set cn = Nothing
Exit Sub

Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler

End Sub

It starts out great but then it returns this error message....???!!!


Invalid SQL Statement; Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I don't know how to fix the error!!!! Please help!

Tiffini

 

[spacepro]

do you have the combo's rowsource set using an sql statement. I think if not mistaken, and people please correct me if I'm wrong but for the code to work, the rowsource needs to be a table/query for the code to run.

Andy

 

[Tiffini]

The Row Source Type is set to Table/Query, and the Row Source has the SQL statement:

SELECT [Contact Info].[ID], [Contact Info].[Contact Name], [Contact Info].[Legal Name] FROM [Contact Info] ORDER BY [Contact Info].[Contact Name];

I don't know what I am missing. Maybe I need to add something?

I need to mention also that I am using MS Access 2000. I am not sure if there is something different I need to do because of this?!

Tiffini

 

[spacepro]

Try removing the brackets in the sql statement so that it reads like this:

SELECT Contact Info.ID, Contact Info.Contact Name, 
Contact Info.Legal Name FROM Contact Info ORDER BY 
Contact Info.Contact Name;

Also just a tip, when you are designing the db try not to name tables with spaces in, your table shoud be tblContactInfo. If you do a search on naming conventions in the forum there are plenty of posts explaining this.

Andy

 

[Tiffini]

Thanks Andy! I wondered if the names of my tables were giving me problems as well?! I do remember the rules from VB, but I wasn't thinking about it when I started creating the database...next time, I'll know better!
What about the "Not In List" code in regards to Access 2000? Should anything be different there?

Thanks again,
Tiffini

 

[spacepro]

I think the code is ok, but I am not that familair with ADO, as I prefer to use DAO.

Try it, if you have any problems post them and I will try and help.

Andy

 

[Tiffini]

The SQL statement returned an error of missing syntax...so I put the brackets back in...I am getting an error of User-defined type not defined. So...I think it has something to do with 2000....but I can't figure out what to change in the code.?????

 

[spacepro]

Right, it sounds like the reference to ADO isn't set in VBA.

Go to the VBA editor and select

Tools > References

Then select Microsoft Activex Data Objects from the list.

Andy

 

[spacepro]

If this doesn't sort it, post the line of code where the message is coming up.

 

[Tiffini]

Well, I am much, much closer...however now I get an error at the very end. Error #13, Type mismatch...

Private Sub Combo32_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_ErrorHandler

' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf

' conenction and recordset object variables

Dim cn As Connection
Dim rs As ADODB.Recordset

' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "ContactInfo", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("ContactName") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset connection
End With
Response = acDataErrAdded ' confirm record added
Else
Me.Combo32.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If

Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set cn = Nothing
Exit Sub

Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler

End Sub

Thanks,
Tiffini

 

[Tiffini]

If this doesn't sort it, post the line of code where the message is coming up.

That definately helped...but now at the end...I get an error #3625 : Item cannot be found in the collection corresponding to the requested name or ordinal.

Any suggestions? I've tried some things with now luck!

Thanks,
Tiffini

 

[spacepro]

Tiffini,
Which word is highlighted in the code debugger.

 

[Tiffini]

Tiffini,
Which word is highlighted in the code debugger.

Actually, the error didn't take me to the debugger. I figured it out...and it seems to be working fine. This time...it was my own error, bad typing! Thanks for all of your help!!!!

Tiffini