MS Access/VB database - DATABASE ADDED

M

mitch37

Ok, so the database is now moving on...I have been working on this and in my FailureInfo form, i have a combo box to select the equipment and the person. However, when i select and equipment, and then a person and try add the date, it 'moans' at me.

P.S. If a select data in the FailureInfo form, and then create a report, will it tie all the info together. I did put some relationships in. I hope they are correct.

:thanks::thanx::magic:
 

Attachments

  • ABC_Database_with_Subform.rar
    272.6 KB · Views: 1,278
Last edited by a moderator:
A

alex_bell

Re: Requery in MS Access/VB

Put the requery code on the click event of the button after the code to delete the record.

So
Code:
strSQL = "Delete * From TableName Where FieldName = '" & forms!formName!ControlName & "'"

currentdb.execute (strSQL)

Forms![Person]![List19].Requery
 
M

mitch37

Re: Requery in MS Access/VB

Put the requery code on the click event of the button after the code to delete the record.

So
Code:
strSQL = "Delete * From TableName Where FieldName = '" & forms!formName!ControlName & "'"
 
currentdb.execute (strSQL)
 
Forms![Person]![List19].Requery

Thank You so much for your help. I thought i understood what you said but i seem to be clueless as i get all sorts of errors every time I enter code.

If my Table Name is 'Equipment' and my form name is also 'Equipment' and my fieldname is 'List15', how would the code work? sorry to sound a bit 'dumb':thanx::eek:
 

CycleMike

Registered Visitor
Re: Requery in MS Access/VB

You can use:

me.requery


"me" is a variable that refers to the form that code is behind.


Also, take a look at www.tek-tips.com
There are dozens of forums there for coding and the Access VBA forum is really good.
 
Last edited:
A

alex_bell

Re: Requery in MS Access/VB

You seem confused.

Your saying you form is called 'equipment' but in the example you posted your form is called person, and on the form person you are referring to a listbox control called 'list19'.

List15 also sounds like another listbox not a table field name.

So lets try help you out.

When you have your form open, select an item in your listbox and then goto the visual basic editor and goto the immediate window, if this isn't on the screen press 'ctrl-G'.

In the immediate window tyep
Code:
debug.print forms!person!list19.value

This should return a value below yourstatement. Then goto your table and open it and then find the record which you had selected. Once you have found the record look at which column the value returned by the debug.print is in, once you have found this column look at the fieldname for it and make a note of it.

Put the info into the code supplied above and it should work. If you are getting error messages include them in any replies. If your just totally confused attach the database and I'll do a few examples for you.
 
M

mitch37

Re: Requery in MS Access/VB

theanks man i will try what you said. sorry there are two seperate forms. One is 'Equipment' and one is 'Person'. They both need to be 'refreshed'. Heading over to my Access now :).....
 
M

mitch37

Re: Requery in MS Access/VB

You seem confused.

Your saying you form is called 'equipment' but in the example you posted your form is called person, and on the form person you are referring to a listbox control called 'list19'.

List15 also sounds like another listbox not a table field name.

So lets try help you out.

When you have your form open, select an item in your listbox and then goto the visual basic editor and goto the immediate window, if this isn't on the screen press 'ctrl-G'.

In the immediate window tyep
Code:
debug.print forms!person!list19.value

This should return a value below yourstatement. Then goto your table and open it and then find the record which you had selected. Once you have found the record look at which column the value returned by the debug.print is in, once you have found this column look at the fieldname for it and make a note of it.

Put the info into the code supplied above and it should work. If you are getting error messages include them in any replies. If your just totally confused attach the database and I'll do a few examples for you.

Hello

Sorry i just typed a whole explanation and my computer stopped responding. It may have seemed like i disnt understand a thing..lol..but i just struggled to figure out what to put where in the line """strSQL = "Delete * From TableName Where FieldName = '" & forms!formName!ControlName & "'" regarding the * and the ''''''s. So let's say if the form name is EQUIPMENT, the ControlName is List15 and the TableName is EQUIPMENT ID, what would that line look like?.

Thanks for your patience :) K+:agree:
 

CycleMike

Registered Visitor
Re: Requery in MS Access/VB database

You might want to post your code so we can see what you're trying to do.
 
M

mitch37

Re: Requery in MS Access/VB database

Code:
Option Compare Database
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "MainMenu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command11_Click:
    Exit Sub
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
    
End Sub
Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

    DoCmd.GoToRecord , , acNewRec
Exit_Command12_Click:
    Exit Sub
Err_Command12_Click:
    MsgBox Err.Description
    Resume Exit_Command12_Click
    
End Sub
Private Sub Command13_Click()
On Error GoTo Err_Command13_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command13_Click:
    Exit Sub
Err_Command13_Click:
    MsgBox Err.Description
    Resume Exit_Command13_Click
    
End Sub
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Command14_Click:
    Exit Sub
Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click
    
End Sub
Private Sub Form_AfterUpdate()
[B][U]Forms![Equipment]![List15].Requery[/U][/B]
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
[B][U]Forms![Equipment]![List15].Requery[/U][/B]

End Sub
Private Sub List15_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EquipmentID] = " & Str(Nz(Me![List15], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub List15_BeforeUpdate(Cancel As Integer)
Forms![Equipment]![List15].Requery
End Sub
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_Command18_Click:
    Exit Sub
Err_Command18_Click:
    MsgBox Err.Description
    Resume Exit_Command18_Click
    
End Sub

The above is for Equipment.

P.S. It's the 14th...Big Day for me :):magic:
 

CycleMike

Registered Visitor
Re: Requery in MS Access/VB database

So, you want to select an Equipment ID from a drop-down list. When you click one the form goes to that record. Now you want to be able delete that record and have the entire form refresh and no longer show that record?

Should work if you add the line to this:

Code:
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    [COLOR="Red"]me.requery[/COLOR]


Exit_Command14_Click:
    Exit Sub

Then get rid of these:

Code:
Private Sub Form_AfterUpdate()
Forms![Equipment]![List15].Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Forms![Equipment]![List15].Requery
End Sub

Private Sub List15_BeforeUpdate(Cancel As Integer)
Forms![Equipment]![List15].Requery
End Sub


Also, something I've learned the hard way.........................

Name all your controls, txtboxes, listboxes, command buttons, etc. something that makes sense so you can more easily follow and debug your code.

Text boxes should be named: txtName
Command buttons: cmdName
Combo Boxes: cboName
Forms : frmName
Queries: qryName
Tables: tblName
Etc.

You can rename them now but you have to also change the name in your code to match.


Let me know if it works.

If you want, send me a copy of the Db with some data in the tables, in a zip file and I'll take a look at it.

mgeitner<removethis>@waukeshastamping.com
 
Top Bottom