Modules & VBA :: Opening New Form And Taking Data From Another
Jul 19, 2013
IThe db has a form called ClientFormNew which logs client details into a table called Clients.Each client has a unique client ID in the table called ClientID. Sometimes Clients call back for further information and we want to log that information into a related table called ClientHistory.Each record in the ClientHistory table has a unique ID called HistoryID which is the same number as the ClientID so these fields are related.
When a client calls back and the telephone adviser opens up the ClientFormNew form and discovers they have called before they need to click on a button called Client History which then opens up the ClientHistoryForm..I am struggling with the code to put on the Client History button as I want the new form to display the details of the clients first contact then allow details of the second contact to be added in a new row. Also the new form needs to show all that client past history which would be in the form of the following fields from the ClientHistory table.HistoryID, ContactDate, Name, ContactMethod, ContactReason, AdviceGiven, Notes.
I have a database that had some code that enabled the user to click a button and it would take information from one Access query or table and paste it into specific locations in Excel. This is important because the last worksheet has specific formatting in place.
If it didn't, I'd just use the transfer spreadsheet option and not even bother opening Excel at all with code.
I've got it working well except for the last part. The last query being copied has eight records and only one is being sent to Excel. Same code as the other queries where all of their data is going to the right spot in Excel with the right number of records.
In my database there are fields of type Date/Time, their default value is set to now(). But the problem is that those fields are automatically filled up by English system date. I want my own country's date format, without changing my system date format. So I decided, whenever database is opened it should ask for a date, so the user will give today's date and that date will be used automatically on those fields.
I have a form that I am inputting data into. I have been able to Clear the form. I have created an edit script and it brings the data from the table to populate the form. When I try to change the data in the form and run a VBA update to push the data down to the table I am having no luck. I can add/Delete recordswith no problems but I can't edit them and put them back in the table. I am novice and just learning the formats and scripting.
I have a quick query. I have a table with a Account Code and its relavant Account Name.
What im tyring to do is in a query or another table, which has 17000 rows of data and the account code has been used in each entry. What i'm trying to do is to be able to create another column where the relavant Account Name can be next to Account code in the query or table.
I am attempting to open a website hyperlink, some of the fields contain https:// and some of them dont.
Code: Private Sub Facebookbut_Click() Dim Hyper As String If InStr([TEAMFacebook], "https") Then Hyper = Me.TEAMFacebook Else Hyper = ("https://www.facebook.com/" & Me.TEAMFacebook) End If Application.FollowHyperlink Hyper End Sub
So far this does 2 things, it doesn't open any hyperlinks at all and continues to attempt to until the program is closed from task manager. Before I had this error it would open the hyperlink twice if the field does not contain "https" and the IF statement was passed to the 2nd option.
I am having a problem where I have a form that I enter data into. Sometimes when I enter the information it will add a record to the table and sometimes it doesn't. It doesn't put the information in the table more times than it does.
I have tried to enter the information outside the form and it still does the same thing.
Do you have any suggestions of what I can do to fix this.
I have a database set up with charts which plot information about support for "quality" purposes. I have reports that work out the percentages but i would like to be able to take the percentage figure (say on the 1st of each month) and add it to a graph which will then be able to show the figures from the last 12 months so i can see how well the support is performing. Any help would be greatly appreciated
I have been looking for quite some time for some vba code that would return a date based on values in 2 fields.
In other words, I already have a field that returns transit days based on the customer chosen. End users will then enter a DueDate for the order. I want a field that returns a "ScheduledShipDate" based on [DueDate]-[TransitDays]. The part that makes this more difficult, in my opinion, is the fact that I also need to take into account some specific holidays.
I have already constructed a table with a list of the 6 company holidays and their corresponding dates. [tblHolidays.HolidayDate]
Unfortunately, my vba knowhow might as well be limited to copy and pasting as I do not need to use it all that often.
Here's my Goal: To open a saved query that has a parameter, setting that parameter via a VBA sub.
Here's my Problem: I was getting various errors, but after debugging my program a bit, it comes down to a "Data Type Conversion Error"
Here's my Code:
Set db = CurrentDb Set qd = db.QueryDefs("qryMY_DATA") qd.Parameters(0) = Me.txt_ReferenceID Set rs = qd.OpenRecordset("qryMY_DATA", dbDynaset)
Code: '*** Database Variables Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Recordset
I've been all over the forums and tried several different approaches, all to no avail. The Query runs fine in the QDT, but kicks back an error when I try to run it from my sub.
I have been tasked with creating a tool to analyse mobile phone bill data and present the analysis, and our recommendation, to a customers. Being new to Access (other than basic tuition) this has been a slow uphill task, which is finally nearing completion, however there is a problem which I have not yet been able to overcome.
The requirement is for the DB to open first on a splash screen (lets call it Form A) with fancy picture where our customer is selected from a combo box, the customer is then telephoned, a linked computer screen is established and our staff then click "Go" to proceed to a second form (Form B) showing an account overview and more details.
The problem I have is when "Go" is clicked, the second form loads via on click event, and even populates the correct customer in its combo box. Unfortunately that is as far as it gets - the combo does not look up the information. The customer needs to be selected again for the subforms and subreports to load with the customer overview. To clarify, form B just sits there blank until the customer is re-selcted from the combo box in form B.
i have a database and i need to be able to read all the records from table1 modify the data and output the data to table2 and I would prefer this to be done via just one button in a form so im guessing VBA need to be used.
table1 consists of an ID field, firstname field, lastname field and date field.
table2 has ID field, full name field, date field
so i want to read the first and last name and date from table1, merge the first and last name and then output the merged names and date to table 2.
ive googled around and all ive come up against is recordsets but im having a hard time actually getting them to even work.
I'm trying to have a form sorted in a particular order when it's opened.
When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.
However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.
So I tried to add the following code :
Code: Private Sub Form_Open() Me.OrderBy "employee_name ASC" Me.OrderByOn = True End Sub
I am using Access 2010 and want to open a new form based on an ID from a previous form. Access makes this very easy to do in the macro builder but I am unable to replicate this in VBA afterwards. The reason behind this is I want it to also run some code to export some info to Outlook. I am only filtering the ID from one form to the next. The ID is from a single table.
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?
What I have is a database that I am using for 3 different sites; NE, NCA, & SCA. What I want to do is have the form ONLY show information for the specific site that the Leader is a member of. I have a security table that contains the persons name, & site location. For example: John Smith works at the NE site; so when he goes to open a form to add/modify/delete members from his site I want him to only be able to see date for NE from the table. So in the listbox NCA & SCA would be filtered.
How can I go about doing this? The forms Record Source is set to the Security Table. Please help so I can do this without having to create a seperate form for each different location. :)
I have a table that has two forms linked to it - one is the Main Form which is used to input the data for the bulk of entries, however on some entries there will be some additional data required which the other form (lets call it Time Form) takes care of.
What I would like to do is have a button on the Main Form that opens the Time Form, and then go to the specific record that is open on the Main Form;I have an ID field (Autonumber + Primary Key) which displays in a text box on the main form, and it would provide the number of the record for the second form to open.
1. I'm in need of some way of preventing a user from opening form B unless he has entered information in form A.
Right now it is possible to open form B without entering data in form A (form A being the date/time info and form B being the case technical info) due to which a record is created for clientnr 0 (which should be a non existing client). Querying for clientnr 0 from time to time and deleting those records is easy enough, but who knows what kind of relevant data could be stored accidentally in a record that doesn't belong there.
This happens by the way, even though referential integrity is enforced throughout the dbase. Maybe I should say, because of that. I want to keep it that way of course to prevent orphans roaming around. But having orphans for clientnr. 0 is also not a good idea.
2. What is the wiser thing to do: keep all information concerning one case for one client (date/time info per case, techinfo per case, maybe even products sold info per case, payment info per case) in one table and have the data needed for each thing entered in seperate forms, or have all data split up into seperate tables like I have now. One for clients, one for dates and times, one for the technical info for that case etc.... ?
It is starting to seem a bit ehrm... useless to have all that data for one case floating around seperate tables. I don't know... it seems so much more complex (having to create multiple relations, multiple PK's per table etc.).
Where do I draw the line? For example the image attached (relationship2.jpg): now I have one PK in the table holding the clients (clientnr), two PK's in the table holding the case date/time info (clientnr and casenumberdatetimeinfo), three PK's in the table holding the techinfo (clientnr, casenumberdatetimeinfo and casetechinfonr). And what's next? Four PK's in the table holding the sold productsinfo? Five PK's in the table holding the invoice info? And what about the relations between those tables? Right now I can still comprehend.... but when I start thinking ahead, I'm starting to get dizzy. So some advice on this would really be highly appreciated.
3. Which build up of the relations between the tables in the dbase is better? The one in relationship.jpg or the one in relationship2.jpg?
I have been having problems with opening a form to add new records only. I have gone through all possible solutions but somehow the problem persists.I want to add new records only and not view any of the existing records. I have set the form properties to Date Entry Yes, Allow additions to Yes, Allow Edits to No. The form opens through a command button on another form and the vba code is the following.
I am trying to program a button on my ms access form to open up an excel file.. So far the simplest code ive found online was from URL....
<code>
Private Sub Command57_Click() Dim xlTmp As Excel.Application Set xlTmp = New Excel.Application xlTmp.Workbooks.Open "C:Excel1.xls" xlTmp.Visible = True
[code]...
However the code doesn't work, any way to open a file from within access?
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer Dim tempString As String Dim BaseQueryFormStr As String 'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters tempString = "WHERE"
