Hi
I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz.
RMA
i have created a new utility DB that has only one form..when it opens I set the startup form "Main" and on the OnOpen event of this form I have some code to open another database and close this one...
this means that whenever i open this database, it opens, opens up the other one and this utility DB closes...and i cannot access it coz it always opens and closes automatically...how can I stop it from executing to be able to edit its VB code?!
Thanks, this is driving me mad !
Hi everyone,
This is my problem. Almost all the time(90%), I open the main form of my application and a large part of the form is blank(white). If I open it from the Database window it often work well and all the form is visible.
When the bug occur, If I just switch from Access to another application like Internet Explorer and I come agian to Access then all the form is visible.
Is someone ever seen that kind of bug. What that can cause that problem. How can I solve it?
N.B. There is a lot of controls on my form and I guess that bug can be linked with it. Seems like Access is trying to open my form too rapidely.
I have a query, it joins a local table to a remote table (Oracle Database Table) which makes it slow(ish).
When I am in design mode and i click the "View Datalist" button, which visually shows me the results, the query executes in about 12 seconds.
When I use the "!" Button to physically Execute the Make-Table Query, it takes hours.
What things should i look for that might cause this problem? Where do I need to optimize.
To me it seems rather odd that the query would execute so fast when i view the data than when it physically executes the data.
Same speed problems when i just run the query as a Selection Query as opposed to the Make-Table Query, so it's not an issue of writing to disk/etc.
whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
I am trying to take input for my reports based on a query result dynamically for which i dont want to display the Datasheet after the execution of Query ,inturn i am displaying results in Report. Any help on this would be appreciated.
View 14 Replies View RelatedI have a button that opens a form based on a query, the query is filtered based on an input field. This works great except when the user typos what they put into the input field. Since the query pulls no records it just opens a blank form. I want it to not open the form and instead open a message box telling the user the data they entered is not valid and to try again.
View 1 Replies View RelatedI have the following tables:
Supplier
Supplier# [Other columns]
Material
Supplier# Material# [Other columns]
DeliveryHeader
Delivery# Supplier# [Other columns]
DeliveryDetail
Delivery# Supplier# Material# [Other columns]
I've created a form based on table DeliveryHeader with a subform based on table DeliveryDetail. The two are linked by Delivery# Supplier#.
On the form, Supplier# is a combobox that lists all suppliers from table Supplier.
On the subform, Material# is a combobox that lists only the materials supplied by the supplier selected with form's Supplier#.
The problem is that this second list always shows the materials of the first listed supplier. For instance, when the form is loaded, it shows delivery #1 with all its details on the subform. Suppose supplier loaded to form is #1.
1) If I use subform combobox, it shows correctly supplier's #1 materials. But then, whatever delivery I navigate, it always shows supplier's #1 materials.
2) If I navigate to another delivery without using subform combobox, suppose I stop on a delivery where the supplier is #4, then subform combobox shows correctly supplier's #4 materials. But then, once again, whatever delivery I navigate, it always shows supplier's #4 materials.
I've spread Me.Requery here and there but without any success. What trick must I apply to force the execution of the query of the subform combobox Material# each time I navigate to a new record on the form?
Hi Folks,
I am wondering if it is possible to execute a DTS package via Microsoft Access. I have done a bit of research and cannot seem to really find anything that claims this is possible or how to go about to get it to work. I am running SQL Server 2000 which has quite a few DTS packages ... it would be really neat if I could just create a small interface in Access whereby a form has a few buttons on it that can be clicked and the DTS packages can be executed on the server, this way folks won't need to log on to the server and run the DTS packages via SQL Server Enterprise Manager. What would REALLY be awesome would be if values can be passed between SQL and Access but I think if that is possible it would be quite an animal to achieve (I know it is possible via Visual Basic programming but not really worth all the work to create an independent program just to run a DTS package or pass a value). If anyone can provide me with any information or where to look I would appreciate it.
Thanks,
Joe
In my db I have a password log-in for each user. During the log-in process, I have a query set to run:
Code:SELECT tblSetOut.LogNum, tblSetOut.WoNum, tblSetCont.SetContName, tblSetOut.OrdAssign, tblSetOut.WoStat, tblSetOut.ThirdParty, tblSetOut.DateIn, tblSetOut.DateOut, tblSetOut.ThirdTimeIn, tblSetOut.ThirdTimeOut, (DateDiff("n",[ThirdTimeIn],[ThirdTimeOut]))/60 AS [Release if Greater than 24]FROM tblSetCont INNER JOIN tblSetOut ON tblSetCont.SetContInitials = tblSetOut.SetContInitialsWHERE (((tblSetOut.WoStat) Not Like "released" & "*") AND ((tblSetOut.ThirdParty)=True));
It opens up in read-only form that the user can print or exit out of.
What I need to happen is for the query to run based on the user's log-in name. There are three tables involved (tblSetOut, tblSetCont, and tblSecurity). The tbl SetOut and tblSetCont are joined at the field SetContInitials. The tblSecurity (field SC_NAME) is joined to tblSetCont at SetContName.
For the life of me, I cannot figure out how to capture the log-in name to filter the query.
Thank you in advance
Camille
Hi,
how can i create a comand button that open a form at predifined date?
example: if current date=2005/14/12(yyyy-dd-mm) then open form "example"
thnks,
Kopas
Hi,
How can I stop user from closing the form if the mantory fields are not filled up? I try using gotocontrol but it is not working.
Hi I've been having this same problem for a while and I've had a bit of help here but none of the suggestions have so far worked.
Basically I need a way to stop people tabing through a sub form without entering data. The sub form has a check box that has to be checked (it can't be set as True as default) the required field doesn't work and for some reason the Enforce Referential Integrity on the join between the main table (tblCustInfo) and this one doesn't work either.
tblCustInfo = the main form
tblAgentData = subform
can anyone please help?
i wonder if anyone can help with this problem i cant seem to work out.
i have a bound form linked to an orders table, including some required fields. when i use the navigation buttons of the form to scroll thorugh the orders i can get to the end where the form expects you to make a new order. but i have a button to add a new order.
i want to stop the user being able to go past the last record in the orders table. or enable them to get back to the last order. currently if the user goes past the last record (making a new order) there is no way to get back to the last record. using the navigation back button causes errors as the required fields are blank and access tries to save the order, thinking i want to make a new one.
i hope this makes sense.
i have tried setting the Allow Additions setting of the Form to No. but this causes problems when there is no data in the orders table. opening the form produces a blank form, showing no fields or buttons at all.
i would be greatful for any advice, maybe i am doing something fundamentally wrong?:confused:
i wonder if anyone can help with this problem i cant seem to work out.
i have a bound form linked to an orders table, including some required fields. when i use the navigation buttons of the form to scroll thorugh the orders i can get to the end where the form expects you to make a new order. but i have a button to add a new order.
i want to stop the user being able to go past the last record in the orders table. or enable them to get back to the last order. currently if the user goes past the last record (making a new order) there is no way to get back to the last record. using the navigation back button causes errors as the required fields are blank and access tries to save the order, thinking i want to make a new one.
i hope this makes sense.
i have tried setting the Allow Additions setting of the Form to No. but this causes problems when there is no data in the orders table. opening the form produces a blank form, showing no fields or buttons at all.
i would be greatful for any advice, maybe i am doing something fundamentally wrong?:confused:
I'm new in Databases and Access, so my problem is very simple.
I have a table with three fields: Name (Prim. Key), Start Date (Prim. Key) and Cost.
I want to obtain a query with this fields:
Name
Start Date
End Date (where the End Date would be one day before the next start date)
Cost
Thank you
I have a problem about running SQL statement in VBA code. I will appreciate a looot if you can help me solve the problem ASAP.
I tried to run the following statement in my VBA code to update a table in my database:
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
It turns out that the table will not be updated if I run the above SQL statement with my other VBA codes. (ACCESS doesn't give any error messages even though the database was not updated.) But if I run the SQL statement by itself rather than within the other codes, the database will be updated successfully. (When I set a breakpoint at the above SQL statement and run it manually within the other codes, the database are updated successfully too.)
Apparently, the SQL statement and the other codes don't have any syntax errors. I wonder why ACCESS doesn't execute the SQL statement when the SQL statement is put within the other VBA codes. Is it because the table it tried to manipulate was locked by the other codes?
The following are all the VBA codes including the SQL statement I'm talking about. When I run the following codes together, the database is not modified by the SQL statement (the last sentence). But if I run the last sentence and the other codes separately, the database was modified successfully.
Dim rstDataLoad As New ADODB.Recordset
With rstDataLoad
Set .ActiveConnection = cnnSHELL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT TBL_GRANTBASEDATA.* FROM TBL_GRANTBASEDATA;"
.Open
End With
'Calculate Retire_Date
rstDataLoad.MoveFirst
Do While Not rstDataLoad.EOF
Calculation omitted.
rstDataLoad.MoveNext
Loop
rstDataLoad.UpdateBatch
rstDataLoad.Close
Set rstDataLoad = Nothing
DoCmd.RunSQL "UPDATE TBL_GRANTBASEDATA, TBL_QUARTER SET TBL_GRANTBASEDATA.Retire_QTR = TBL_QUARTER!QUARTER WHERE ((([TBL_GRANTBASEDATA]![Retire_Date])<=[TBL_QUARTER]![End_Date] And ([TBL_GRANTBASEDATA]![Retire_Date])>=[TBL_QUARTER]![Start_Date]));"
I'm using hte Shell() function but this doesn't wait for the executable file to finish so I'm looking for a command line function that would wait until the executable file is finished executing...
I saw this function WaitFor but it is not supported in MS-Access? So, what do I do?
Thanks...
I have this segment of code that adds data to fields FName and FPath in the table Files. It works fine but the syntax is a nightmare with all the double quotes and symbols.
Code:
strSQL = "INSERT INTO Files " _
& " (FName, FPath) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """;"
CurrentDb.Execute strSQL
I have another field in the table Files called CompletedBy that shows who updated the table (they type their name into a textbox Text1 before clicking a command button to run the code). I would like to add this data to the table during the execution of strSQL. All the records added to the table Files will have that value in the CompletedBy field (say, Bob Jones).I tried the below changes but the code doesnt work.
Code:
strSQL = "INSERT INTO Files " _
& " (FName, FPath, CompletedBy) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """ & Me.Text1 & """;"
CurrentDb.Execute strSQL
How would I add a value in field CompletedBy, from the textbox Text1, to all records added to table Files when the code is run?
Hi there everyone,
Must be a really simple one, this one but can't seem to find the solution! :o
Can someone put me out of my missery and tell me how to make each form open in the same window instead of ending up with millions of open windows??!!
Thanks, Matt
Is there anyway to stop the user from beng able to move a form. I.e. as in change its position on screen.
I am aware that you can do this by setting the border type to "none". However, I want my form to have a title bar along the top, so I cant use this method.
Is there another way to do this?
Thanks in advance.
EDIT: Ive found some information on the "movable" property, but I am using access 2000 and cant get it to work.
Hi people. I'm having fun with this one. I want to open a form frmOldRec from a list on frmSelectOld, only if there is data. I'e tried code such as:
Private Sub Form_Load()
Dim rst As Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount = 0 Then MsgBox "No Records logged in this service area"
DoCmd.Restore,
'close etc etc
End sub
But I haven't been successful.
As it stands it opens a blank form if there's no suitable record. How can I stop this happening please?
Cheers Barry
Hi
I have set up a tabbed form to preview data going onto a report. There are four sub forms each on its own tab. I have locked down the data on the sub forms.
However when I open up the tabbed form the focus seems to shift to the first control on the tab. Ideally I want all the data controls on the tab to remain without focus leaving only the tab controls and the close form control that can get a focus.
Is there any way I can do this, or am I not making myself clear?
It isn't a deal breaker but it makes the form look a tad ugly!
Thanks for any help.
I have a macro that opens up a form called SLA Complete Form - The macro then goes and examines two fields on the form to see if they contain data and if they do not then it displays a popup box with a message.
The form that gets opened up can have anywhere from one record to approximately a hundred records.
How can I get the macro to stop running when it hits the last record on the form without displaying an error message. I am currently using the next record feature in the macro.
Hi
how do I setup a "welcome" form to start on database start?
miha
I just started working for a company the other week and they threw this query onto my lap with an unrealistic deadline. My programming experience is primarily in VB6, so I am not completely familiar with coding Access queries.
I was given code that was written for SQL, and the programmer that came up with it is unfamiliar with Access, so I am stuck trying to decipher this. If anyone could help me out, I would greatly appreciate it, as I am thoroughly confused and don't know where to begin. Here is the programmer's original code:
while exists(select top 1 accNum from NoteFile where patindex('%' + char(10) + '%',dbtrref) > 0)
begin
--Insert note into table
insert into #TL19Note(AcctNumber, Note)
select accNum, (case when patindex('%' + char(10) + '%',
dbtrref) <= 0 then dbtrref else left(dbtrref,patindex('%' + char(10) +
'%',dbtrref)) end)
from NoteFile
--Delete note from Note String
update NoteFile
set dbtrref = right(dbtrref,len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) + '%',dbtrref))))
where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) > 0
--Delete rows with no more notes
delete from NoteFile
where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) <= 0 or patindex('%' +char(10) + '%',dbtrref) <= 0
end
--Eliminates any that are CRLR or just spaces
delete from #TL19Note
where len(Note) < 10
--Update the date
update #TL19Note
set NoteDate =left(Note,patindex('% %',Note)-1),
Note = right(Note,len(Note)-patindex('% %',Note))
--No '@' means no time?
update #TL19Note
set NoteTime = '0000'
where left(Note,1) <> '@'
--delete the '@' - we know which ones have it because NoteTime is null
update #TL19Note
set Note = right(Note,len(Note)-patindex('% %',Note))
where left(Note,1) = '@'
--Update NoteTime, take time out of note
update #TL19Note
set NoteTime = left(Note,2) + substring(note,4,2),
Note = right(Note,len(Note)-patindex('% %',Note))