MS Access/VB database - DATABASE ADDED

M

mitch37

#1
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

Last edited by a moderator:
Elsmar Forum Sponsor
A

alex_bell

#2
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

#3
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
#4
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

#5
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

#6
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

#7
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:
 
M

mitch37

#9
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
#10
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
 
Thread starter Similar threads Forum Replies Date
E Document Control MS Access Database Document Control Systems, Procedures, Forms and Templates 42
I How to build a Microsoft Access MDB Database for Document Control Document Control Systems, Procedures, Forms and Templates 6
N Database (Like ASSIST) to subscribe to & have access to new SAE-AS & ANSI standards Various Other Specifications, Standards, and related Requirements 2
T Microsoft Access Database to create a Simple Calibration Certificate General Measurement Device and Calibration Topics 6
T Basic Document Control Database (Excel/Access) Document Control Systems, Procedures, Forms and Templates 3
A Does anyone use FMEA database in Access? Quality Assurance and Compliance Software Tools and Solutions 2
C Looking for CAPA Database for Access 2003 Quality Assurance and Compliance Software Tools and Solutions 5
A Access Document Control database wanted Document Control Systems, Procedures, Forms and Templates 1
L Developing an Access ECO Tracking Database - Need help Document Control Systems, Procedures, Forms and Templates 8
A Calibration and PM Access Database Calibration Frequency (Interval) 3
P Creating a Traceability Database with Microsoft Access Document Control Systems, Procedures, Forms and Templates 7
A Access Database for LPA (Layered Process Audit) questions Process Audits and Layered Process Audits 3
T MS Access Document Control Database advice and database wanted Document Control Systems, Procedures, Forms and Templates 2
L Access to the Italian Ministry of Health's database (the Italian repertorio) Other Medical Device Regulations World-Wide 5
B MS Access database in your Quality System? Quality Assurance and Compliance Software Tools and Solutions 19
W Change Control Metrics - Looking for Excel or Access database Document Control Systems, Procedures, Forms and Templates 5
C Microsoft Access Database for In-Process Run Charts Quality Assurance and Compliance Software Tools and Solutions 4
M Macro to connect to Access Database to get to data - Minitab Macro help Using Minitab Software 1
J Microsoft Access Integrated Database Question - PPAP Module Software Quality Assurance 13
J Problems with Charting - Access Database Question Quality Assurance and Compliance Software Tools and Solutions 9
M Corrective Actions Database in Access Nonconformance and Corrective Action 43
J Access Database to Track Non-Conforming Material Quality Assurance and Compliance Software Tools and Solutions 8
J Help with Microsoft Access ODBC Database Query Criteria Misc. Quality Assurance and Business Systems Related Topics 35
Q Training Matrix in Excel or maybe an Access database Excel .xls Spreadsheet Templates and Tools 1
S RPN Ranking - Put a formula in an access database or Excel .xls spreadsheet Excel .xls Spreadsheet Templates and Tools 12
T Shop Control - Seeking Access Database Quality Assurance and Compliance Software Tools and Solutions 3
L Access Database for Gage Calibration General Measurement Device and Calibration Topics 24
C Customer Complaints 'Access' Database - See attached for your comments Customer Complaints 17
R Looking for access database to control and monitor concerns & internal figures Quality Tools, Improvement and Analysis 5
D Internal Auditing - Access Database for tracking Internal Audits Document Control Systems, Procedures, Forms and Templates 41
M Access Database for Internal Audits. Control or not to Control? Internal Auditing 10
B PFMEA's and control plans from something like an Access database for TS 16949:2000 Quality Assurance and Compliance Software Tools and Solutions 2
Q Web Page Development and MS Access - How to make a web page do a database call After Work and Weekend Discussion Topics 6
A Microsoft Access APQP type database for Process Flow, FMEA, Control Plan, etc. Quality Assurance and Compliance Software Tools and Solutions 44
V Access or Other Database Development Procedure Document Control Systems, Procedures, Forms and Templates 2
Q FMEA and Risk assessment in MS ACCESS FMEA and Control Plans 2
D Economic Operators, EU Market Access EU Medical Device Regulations 2
Richard Regalado Informational ISO makes available relevant BCMS standards free for online access Business Continuity & Resiliency Planning (BCRP) 6
I Master Document Access - ISO 9001:2015 clause 7.5.3 Document Control Systems, Procedures, Forms and Templates 5
F ISO Certified companies - Is there a list of certified companies that I can access ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 1
C Security and access in cGMP facilities Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 1
P Average access height into an industrial machine (average base plate height) Human Factors and Ergonomics in Engineering 2
K EU MDD Class I Device market access / certification after 26th May 2020 EU Medical Device Regulations 9
M Informational Paper on open access – EU postmarket surveillance plans for medical devices Medical Device and FDA Regulations and Standards News 1
R Demonstrate how sufficient levels of access to data is achieved - Claims of equivalence EU Medical Device Regulations 3
M Informational US FDA – Expanded Access for Medical Devices - Investigational medical devices Medical Device and FDA Regulations and Standards News 1
C Access to Technical Documentation - MDR Chapter VI Article 61 EU Medical Device Regulations 4
J AS9104-1 Clause 18.1 (a) (b) - Organizations (contractor) shall be required to allow access to Tier 2 data AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 7
M Who should have access to Audit trail? Qualification and Validation (including 21 CFR Part 11) 6
A EASA Easy Access Rules for Continuing Airworthiness - June 2017 EASA and JAA Aviation Standards and Requirements 0

Similar threads

Top Bottom