I have a form that is based on dynamic query. There is a "main" form where a user selects search criteria from 2 fields. This then calls another form, where the results are displayed.
I am trying to update the record set at the top of my code in the form. I am getting a prompt to "enter a parameter" when I execute. Can some one help?
Database attached...
Public Function refresh()
' Check for LIKE job id
If Forms!main.job_id > "" Then
varWhere = varWhere & "[JobID] LIKE """ & [Forms]![main]![job_id] & "*"" AND "
End If
' Check for LIKE title and description
If Forms!main.keyword > "" Then
varWhere = varWhere & "[JobTitle] LIKE """ & [Forms]![main]![keyword] & "*"" AND "
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
' Update the record source
Me.RecordSource = "SELECT * FROM Acacia_Joblist_all_knowledge " & BuildFilter
End Function
I have a form that is based on dynamic query. There is a "main" form where a user selects search criteria from 2 fields. This then calls another form, where the results are displayed.
I am trying to update the record set at the top of my code in the form. I am getting a prompt to "enter a parameter" when I execute. Can some one help?
Database attached...
Public Function refresh() ' Check for LIKE job id If Forms!main.job_id > "" Then varWhere = varWhere & "[JobID] LIKE """ & [Forms]![main]![job_id] & "*"" AND " End If
' Check for LIKE title and description If Forms!main.keyword > "" Then varWhere = varWhere & "[JobTitle] LIKE """ & [Forms]![main]![keyword] & "*"" AND " End If
' Check if there is a filter to return... If IsNull(varWhere) Then varWhere = "" Else varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter If Right(varWhere, 5) = " AND " Then varWhere = Left(varWhere, Len(varWhere) - 5) End If End If
BuildFilter = varWhere
' Update the record source Me.RecordSource = "SELECT * FROM Acacia_Joblist_all_knowledge " & BuildFilter End Function
I am trying to setup a webpage for people to update access 2000 db using FP2000 and DRW. I do not want update all fields in a record though - a couple of them are static. I have been unable to do this. I am using code lifted from another page and DB that does exactly the same thing for the update. I either get the error data type mismatch, or I should provide default values for all form related fields, or no records get updated. Is there anyone who can point me inthe right direction? Thanks, Tim
Sorry this is so long but I am trying to make it as detailed as possible...
The Setup
My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).
The Problem
When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".
What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.
My Solution
I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").
I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?
I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.
sql example below. Which is the Access Sql in the Query
I have a database form with a tab control, combo boxes, and subforms. The main form has a combo box with a list of clients. When I first created the form, there were only 20 clients. I recently added 15 more to the client table (by hand - not through the form), but the form has not updated itself with these new records. It acts like the information has not been entered into the tables when it has been. So when I select a newly added client, I end up seeing fields populated with the first client's information.
This is the code I have for the combo box afterUpdate method: ' Find the record that matches the control. Dim rs As Object
Set rs = Me.Recordset.Clone rs.FindFirst "[Clients_ClientID] = " & Str(Nz(Me![Combo50], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I would guess that I would just need to update the recordset...:confused: Any ideas?
i'm getting this message when i try and change a record in a table via a recordset... but i am using CursorType = adLockOptimistic which i thought let you make changes to the table
here is my code so far
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.CursorLocation = adUseServer rst.CursorType = adLockOptimistic rst.Open "TBL_TmpSubmission", CurrentProject.Connection
If rst.RecordCount > 0 Then Do While Not rst.EOF MsgBox rst!PropertyType, vbOKOnly, "debug" If DCount("[PropertyType]", "[TBL_PropertyType]", "PropertyType = '" & rst!PropertyType & "'") <> 1 Then rst!PropertyType = DLookup("[PropertyType]", "[TBL_PropertyType]", "IDPropertyType = " & rst!PropertyType) MsgBox "property changed", vbOKOnly, "debug" Else MsgBox "good property", vbOKOnly, "debug" End If rst.MoveNext Loop End If
rst.Close
am i using the wrong combination or cursor and lock type here? reading the help it seems i should be able to make changes to the table.
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset Then it uses the rs.Findfirst method It checks if rs.Nomatch is not true (so the records exists!) Then it starts updating the record with rs.edit rs!value1 = myvalue1, rs!value2 = myvalue 2 rs.Update There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
Hi guys, I'm a bit confused: I know that opening a bound form will bring down the whole recordset. But if I create a parametered query as the recordsource, will it still bring down the whole recordset first and only afterwards cut down to the records matching the parameters?
Example: I have a clients table with 5000 clients on a backend mdb, and my client lookup form in my frontend mdb. If I open the form looking for just client "Jim Jones" (via the parameter query), will Access bring down all 5000 clients from the backend to my frontend first, and only then seek out "Jim Jones" before discarding the rest of the recordset?
For if this is true then this whole Access business is rather unsuited for networks, even a small one. I hope you guys prove me wrong.
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records: userID / Date u1 / d1 u1 / d2 u2 / d1 u2 / d2 u3 / d2 ... 1st Select Query (where Date = d1): u1 / d1 u2 / d1
Desired 2nd Query based off 1st Query (where ?): u1 / d1 u1 / d2 u2 / d1 u2 / d2
Hello. Recently posted for advice on building an access database on health service training. I had a lot of help but I suppose I haven't really grasped relationships yet. My database looks like this:
I have 1:M tb_staff to tb_training_done and the tb_training_done is linked to the tb_courses 1:1 - not sure if this is right but it's the only way access will do it.
Anyway, my idea was to use a form and subform to enter training done but after setting up the lookup it won't allow duplicate courses i.e. same course done each year. Can anyone explain how the lookup works? Where are the details of courses done stored?
im having a bit of trouble generating a field in one of my queries. i think its fairly simple, all i want to do is have the field in my query to show a date, that is just calculated by subtracting 5 days from the current day. i have something that looks like =Now()-5 but that doesnt work obviously. so im just asking whats the imput going to look like if you want to subtract 5 days from a date? oh and im using short dates for the date inputs
I am trying to build a report that lists all call in's and all tardies on the same sheet. They are two different tables, that I made queries out of because of calculation fields. Do you know how I can include all the needed data in the same report?
What I'm getting when I try is either all call-in's for each name that has tardies or vice versa. I need to see all data on one report
The reason I didn't just put all the data in one table was because we track call-in's by occurence, but we track tardies by pay period. So each call in is logged in the call-in table with the date it happened on, and the tardies are logged in by pay period, so only the total is stored in the field.
Do you have any ideas? I'm really stuck and I've been workin' on it for three days now and I don't think I'm going to find the solution by myself. Sorry. Thanks!
Gee, I know this is probably so simple...I've never in my life worked with any kind of data base program. I've spent 2 hours trying to make it work and it doesn't. I've tried so many things that I can't even remember what steps I did. I'm trying to create a one column list for phone numbers, no names or anything else...the most important thing is that when I add new phone numbers to this list I want Access to alert me that it is a duplicate and not allow me to add it.....first of all it won't let me add entries with dashes such as 312-5964, I tried selecting "numbers" and also "auto number", both didn't work, what am I doing wrong...can someone give me the steps from the beginning please....I don't have time to figure this out and it's holding me back from finishing a task. I have to get working on my Video Professor Learning CD for Access which I have at home, but right now I'm at work and can't do that...hellllllllp! I'm ready to tear my hair out :eek: Thanks, Rosey
I have an Access DB I have to put on numberous laptops. The DB is being transported via a CD. When i try to copy the DB from teh CD to a new folder on the laptop I get messages that the DB may have a virus and it cant be copied, or I need to install a microsoft service bulliten...xxxx ( I am not sure what the number is). The DB does contain VBA code and I am certain it is virus free. Is there some setting in access which enables this file to be copied on laptops which do contain virus or firewall protection or must I disable virus protection on these machines before instlaling the program or is it another issue altogether. Thanks.
I have genreated an autonumber in my table, at this is used as key. after importing 135 records it starts to jump 48 records before it creates a new. This means that with every record i import after 135 i have a space of 48 between each. So what sort of thing can cause this? and even better how do i fix it?
I have been using the forums for the past month and a half, and they have been extremely beneficial to me. I am working on a database that cross references my companies Steel standards to American standards. I am trying to make it searchable in a couple of different ways. I have borrowed code found on this site to make two different search engines. One works very well but the other I am getting hung up when I try to switch from the previous code to mine. below is an example of my code. It is taken from code placed on here previous called "SampleSearch"
Private Sub cmdSearch_Click() On Error Resume Next
Dim sSql As String Dim sCriteria As String sCriteria = "WHERE 1=1 "
'tblSubject qrySearchCriteriaSub If Me![Spec] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Spec = """ & [Spec] & """" End If
If Me![SteelType] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.SteelType like """ & [SteelType] & "*""" End If
If Me![Group11] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group11 like """ & [Group11] & "*""" End If
If Me![Group143] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group143 like """ & [Group143] & "*""" End If
If Me![Substitute1, Substitute2, Substitute3, Substitute4, Substitute5, Substitute6, Substitute7, Substitute8, Substitute9] <> "" Then sCriteria = sCriteria & " AND qrySearchCriteriaSub.Substitute1 = """ & [Substitute1] & """" End If
I apologize also because I do not know how to post this in a scrollable window. If someone could help me with that too.
The problem with the code is that I am using a form to filter a query, this will then display the table of the steels that meet the requirements. However, I have altered all of the fields to my names and the combo boxes have my information, but they do not seem to place any parameters on the query. I attached the original DB that I got this code from any help would be appreciated.
I have a access 2002 database that was handed down to me.. this program does some document control, set up with access rights for different users.I got into it to clean it up a bit and I've hit a snag. I converted the database from a 2000 to a 2002. Amidst playing around with the Log-In screen somehow my Log In button stopped working; except if you type your password and then hit enter.Now the Log In button ( cmdLogIn_Click() ) is pointing at the same exact script as the ( txtpassword_KeyDown(KeyCode As Integer, Shift As Integer) ) function.I don't know any VB and I'm not really familiar with Access, so if anyone could decode this for me I would be really grateful. Here is the script that those two functions are pointing to:Private Sub LogIn_Click() Dim strPassword As String Dim strPasswordAttempt txtPassword.SetFocus strPasswordAttempt = txtPassword.Text cboUsername.SetFocus strPassword = DLookup("[Password]", "User", "[Username] = '" & cboUsername.Value & "'") If strPasswordAttempt = strPassword Then strUsername = cboUsername.Value intAccessLevel = DLookup("[AccessLevel]", "User", "[Username] = '" & cboUsername.Value & "'") bolApprover = DLookup("[Approver]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditDocumentInfo = DLookup("[EditDocumentInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditUserInfo = DLookup("[EditUserInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditReferenceDocuments = DLookup("[EditReferenceDocuments]", "User", "[Username] = '" & cboUsername.Value & "'") bolViewAllApprovers = DLookup("[ViewAllApprovers]", "User", "[Username] = '" & cboUsername.Value & "'") bolDocumentControl = DLookup("[DocumentControl]", "User", "[Username] = '" & cboUsername.Value & "'") ' MsgBox "Your access level is " & intAccessLevel Startup ' run startup sub DoCmd.OpenForm "Welcome", acNormal DoCmd.Close acForm, "Login" LogEvent strUsername, Now(), "Login Successful", "Network Username: " & strNetworkUserName & " on Computer " & strComputerName 'LogEvent strUsername, Now(), "Login Successful", " on Computer " & strComputerName Else MsgBox "That password is incorrect. Try again." LogEvent "Unknown", Now(), "Login Failed", "Attempt: " & CStr(cboUsername.Value) & " , " & strPasswordAttempt & " Network UserName: " & strNetworkUserName & " on computer " & strComputerName End If End SubWhen you click the Log In button it does actually run the script, but it gives me the "The password is incorrect. Try again." message even if the password is correct. I can just click into the txtpassword box and hit enter and it will log me into the database.P.S. I say again, this isn't my code.. its probably super ugly, but I cant' tell :o
Like I stated on the title, this forum got me into trouble. By following the reading in this forum and some recommendations from the regular users I have created thre database for the place I work at. now they want me to be the official DB admin! :eek:
So now I'm coming back to you guys for more advice... :rolleyes: what reading should I do to better my Access knowledge over all.
Any recommendation is welcome.
I have already contacted th local college and they do have a distant learning class for Access and I will be taking it the next semester, but I would like to do some reading into becoming a REAL Access programmer with a solid foundation.
There must be some good books out there to purchase to get me started. So all are welcome to give me some feedback.
Hey guys- I have a software program that I am exporting data out of into excel (it's SQL based). From Excel, I then am importing into Access into an existing table previously created from a similar file (same columns, just different record sets). I am running into problems when importing files...
If I import into a new table, and have Access create the table based off of the Excel file- it works fine. If I try to import more records into this same table, it tells me it failed to import and that an error occured (no error's table created, no error number given). If I delete the original records in the table, and re-import the SAME RECORDS that were originally imported to create the table- it doesn't work. It will only import my files if it creates a brand new table for each import, every time.
Can Access not import into an existing table? It should- but I dont get what's going on here. I have tried DBF, HTML, txt files- all the same results. I really don't want to have to create a new table each time- as now my Queries will have to be updated everytime I create a new table. Can you please help?
I don't how this happened but a crucial field (ClientID) in my Clients table changed from Autonumber to Number. It's the primary key and linked to other tables. Access won't let me change it back...and until i do my entire database is at a standstil - cannot enter new clients.
What I am trying to do is when someone is filling out the first table (tblLoanDetails) they have a dropdown to choose the escrow company, then they must choose the escrow officer
Each escrow office can have many officers, however, when they choose the escrow officer from a dropdown, I only want that offices escrow officers to appear in the dropdown.
I have been asked to redesign a database that tracks a huge number of data points. These are projects and the original table had over 100 fields. I have managed to separate them to related tables in an attempt at normalization. They are:
tbl_workorders (main project info) tbl_services (services ordered) tbl_contacts (internal company contacts) tbl_customers (customer information) tbl_project_dates (milestones of project)
Now this is different from other databases I've designed because all of the tables need a one-to-one relationship with the main table (tbl_workorders).
Am I heading for trouble with so many tables existing in a one-to-one? Also, The table tbl_workorders has its primary key as an autonumber. I want any new order on this to create matching foreign keys on all the other tables...I assume this should be handled since I have enforced referential integrity with cascade on update/delete for all the other tables.
