Hi.
I'm putting together a database of property clients and i have almost finnished but as always i seem to get so very close to the end of a project and i hit a wall.
background:- I have about 5 different tables which mostly have relationships but there is one main table (clientDetails) which holds the main record this has a very predictable clientID primary Key. the other tables have equally predictable keys for the tables ie: LettingID for a letting client insuranceID for insurance clients etc.. the deal is really that EVERYONE is a client so they get the main details filled out and another record in another table using the primary key of the main table in all other tables. one client can have many other records relating to them useing the primary key.
The Problem:- I have made lots of nice forms that all link up together so you can edit and brose all of the records in the database, and this works very well.. however my problem comes when i want to add a new record in one table that relates to the same record in another using the forms not the table view. eg. on form 1 (clientDetails) i add a new client to the database all fine even using the autonum for the PK. now i bring up the record of that client and i have a button (addProperty) this brings up another form (propertyDetails) using the PK from the previous. this works fine if there is already an entry in the DB but if not then the PK is autonumbered..
What i need:- I need to be able to open up a form using PK then (addProperty) button to the next form which finds that record using the pk (clientID) and FK (propertyID) and if there is no FK relating to the PK then to autonumber and use that... so i basically transfer the PK from form 1 to form 2 regarless of any other data relating to it, and auto numbering to the next number in that table for the FK.
eg.
form1 form 2
------- > ---------
PK=100 PK=100
FK=5
this is probibly really basic.. but i hope someone can help ..
Is there a command line argument to open AND close a db? I am running a schedule task to open a db which has an autoexec macro but I don't want the db to stay open after running the macro. Or is there a close db function in a macro? I see close form but not db.
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then MsgBox "Go Away", vbOKOnly Else Go on and do the real work End If End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
I want to be able to pass arguments to an access file on start up. I want to be able to grab this value and then perform an action based on this.
Background: The program will send an email with details for a change request, the recipient will then need to click on a link to accept or reject this change. So I want them to be able to click the link which will trigger the program to save accept or reject.
I have got an unbound access form, and in this form I gather 2 dates (i.e. through txt fields). Once I have these fields, I want to open a form that has one list box on it. This list box should populate based on the dates that I gather on the previous form. How do I specify this through the DoCmd.OpenForm arguments? Currently I have the following code, but it is not working:
Form 1: Dim whereClause As String whereClause = "SELECT * FROM qryInvoice WHERE tblInv.InvDate Between #" & txtStartDate & "# And #" & txtEndDate & "#" & ";" DoCmd.OpenForm "frmInvoiceFax", acNormal, , , , , whereClause
Form 2: public Sub Form_Load(args As String) MsgBox args lstInvoice.Rowsource = whereClause End Sub
I know I am not doing it the right way (because it is not working), but I can't actually find how to do it. Help!
I'm trying to QUERY an ACCESS database called ARQUIVO and i would like to have from the column EMPRESAS all the DISTINCT records that have the same 'aviacao' in the INDUSTRIA column.
and it goes like this: Code:<%Set rse = Server.CreateObject("ADODB.Recordset")sSQL = "SELECT DISTINCT empresa FROM arquivo WHERE industria='Aviacao'" rse.open sSQL,con, adOpenStatic, adLockPessimistic, adCmdText%>
But all I get in a 500 error.
The fact is that if I use "*" instead of "empresa" the query runs but i list all the records with 'aviacao' in INDUSTRIA
I have this SQL query made in ACCESS - that does what I want - but it doesn't rune in mine ASP page.
SELECT DISTINCT arquivo.EMPRESA, arquivo.INDUSTRIA FROM arquivo WHERE (((arquivo.INDUSTRIA)="Cāmaras Municipais"));
Can you help me? I get the error wrong type of arguments in my function as follows Private Sub Command0_Click() Call ANewDBWithPass("c:BELest", "secret") End Sub
Function ANewDBWithPass(ByVal tName As String) '// adds the current date to the name of the database Dim wsp As Workspace Dim db2 As Database Set wsp = DBEngine.Workspaces(0) If Right(tName, 4) = ".mdb" Then ' Remove .mdb from the name tName = Left(tName, Len(tName) - 4) End If ' Add date and extension tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb" ' Create database ',Set db2 = wsp.CreateDatabase(tName, dbLangGeneral) Set db2 = wsp.CreateDatabase(tName, dbLangGeneral & ";pwd=" & strPassword) db2.Close End Function
I have a python script "runAll" that takes two arguments: processID (the primary key of one table as a string) and a filename (a full file name path as a string).
Is it possible to run a python method with arguments from inside a VBA script (activated by a button press)?
My python script is called XMLGenerator.py, and the method I want to run is called runAll(processID,filename).
The procedure call "Call RearrangeCoordinates(RearrangeCoords(), ReturnCoords())" return a message "Type mismatched: Array or user-defined type expected"Are there any views as to why the message is being displayed?
Private Sub CommandButton1_Click() Dim ReturnCoords As Variant Dim ........................................... ArrayLength = UBound(ReturnCoords)
I have a after update event that will match the written record with any exist record in a table in the field "OrgName". If it doesn't find exact match, will call a function with a "soundex" algorithm to see if there is only a misspelling or another name altogether.
In the afterupdate event, I have a string called strOrg (wich is the name I want to compare).
I have the soundex function in a module, so I can use it for several form generally.
What I want is to pass the strOrg to the soundex function, however I don't know how to declare the variables. however I keep having this error:
"compile error: Argument not optional"
and goes to the line tagged as 1 in the private sub afterupdate
The afterupdate sub is the following:
Code:
Private Sub tOrgName_AfterUpdate() Dim strOrg As String strOrg = Me.tOrgName.value If IsNull(DLookup("orgID", "torg", "OrgName = '" & strOrg & "'")) Then resMsg = MsgBox("This organization name is not in the list. If you want to look for similar names press YES, if you want to register a new organization press NO.", vbYesNoCancel, "Organization not found")
[Code] ....
and the soundex function is declared as
Code: Public Function Soundex(strOrg As String) As String Dim Result As String, c As String * 1 Dim Location As Integer
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents Field: RankOrder
Here is what I have:
Code: Private Sub RankOrder_BeforeUpdate(Cancel As Integer) Dim lngRankDup As Long lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0)) If lngRankDup <> 0 Then MsgBox TestEventID & " already exists in the database" End If End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?
Before attempting to create a VBA User Defined Function that will accept arguments for creating UPDATE/INSERT INTO SQL statements, I thought I would check to see if some already exist. It seems like a very tough task to tackle. I'd like it to determine the data type of the the values being placed into the specified fields and subsequently provide the appropriate syntax. (i.e. '" & mString & "', #" & mDate & "#, etc.) Do any exist?
when i open a form i want to open another frm at the same time. i have a main form with some buttons in it. when i click on a button and a form opens then i am not able to click on a button to open other forms from the main form.
Is there a way to close a form that is open from another form that has the focus. I tried using the Unload event, but I received an error message "Cant load or unload the object". I would appreciate any help!
Basically I have a Customer Form, which I have a New party button on it,this button opens up the party form to a new party, what I would like it to do is open up a new party but make the new party for the customer I had selected in the previous form.I have tried the GoTo macro's but cannot seem to get it to work.
I am thinking on clicking the button it will need to get the Customer ID, and then open the party form, create new party, and paste in the Customer ID, which then updates the Name - Date - Address - Company Fields.
I want to open another form automatically when an initial form opens, both are non popup) I have tried open and load events - but still the calling form appears on top of the called form.
How do I get the two forms to open form 1 and then form 2?
Also I cannot use the timer event as this is doing something else.
I have one table containing name of restaurant with its address etc. Then i created another table to list out the restaurant workers names and details. Just as an example,
Table:Restaurant Restaurant name Address line 1 Address line 2 Restaurant # Website
Table:StaffContact Staff Role Name speciality email phone
I have the main form that has all the restaurant details only. And i have another form containing the Staff information. Please note the two table have a relation and it works well.
Now to make it user friendly(basically easier for the lazy ones), I dragged the staff contact form on to my main form and displayed it as a datasheet(basically a sub form).
Now, my boss does not want users to add/delete on this sub form(datasheet). So,he wants me to create buttons to open new record of staff for each restaurant(new form)
My issue is with opening a new record to enter a new person to the staff list and give them a role as well in form view.The new form has
So i ran a Macro, with open form with Where condition
I have a continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.
At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.
continuous form: "04 - GASTOS_BUSQUEDA" id field on continuous form: "Gastid"
pop-up (details) form: "GASTOS_EDITAR" id on pop-up (details) form: "editar_id"
This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):
4) Private Sub btn_editgs_Click() On Error GoTo btn_editgs_Click_Err Dim strWhere As String strWhere = "[editar_id] = " & Me.Gastid DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere btn_editgs_Click_Exit: Exit Sub btn_editgs_Click_Err: MsgBox Error$ Resume btn_editgs_Click_Exit End Sub
I would like to copy one control, then close this form, open concrete form and pass value to control.
My code is
Private Sub Menu_Click() DoCmd.OpenForm "frmZleceniaMarzenaNawigacjaPD" Forms!frmZleceniaMarzenaNawigacjaPD.Form!Imie = Me.Imie Forms!frmZleceniaMarzenaNawigacjaPD.Form!Imie.SetFocus DoCmd.Close acForm, Me.name End Sub
The problem is, still opens the previous form, not form "frmZlecenia"
e.g If I open form x then from this form I open my modal form "frmZlecenia" and then I will click "menu" button - now form "x" is open :/, but should be "frmZleceniaMarzenaNawigacjaPD"
There is some way to open concrete form from modal form?
I currently have two forms: frmE_SAFind and frmE_SAOrder
frmE_SAFind shows results from a query including fields [txtIDPO] and [dtmDate] Example: IDPO Date btnOpnFrm 6543 2/1/05 btnOpnFrm 5681 1/1/05
frmE_SAOrder shows order details including [txtIDPO] and [dtmDate]
I have a open form command button set up on [frmE_SAFind] that opens [frmE_SAOrder]. Is it possible for me to modify its properties so that when the open form command button is clicked, the order details in [frmE_SAOrder] will represent the order that the user is selecting via the btnOpnFrm command?
Example: If I click btnOpnFrm for 6543, [frmE_SAOrder] will show me PO 6543 details.
Hopefully I made myself clear enough to understand. Thanks for your help!
I'm working on the Navigation Form template in Access 2010, which is new to me. It appears that one needs to use the BrowseTo command to open up a form in the built-in subform module. I'm trying to create a couple of buttons where each button opens a form in a different data mode; one in read-only and one in add mode. Here is the syntax I used for read-only:
frmSales is the Sales form I want both of the buttons to open and frmNavigation is the Navigation form. It seems to be ignoring the data mode part at the end however. It only will open in Edit mode. Is there something I'm doing wrong with this command?
I am trying to create a proposal log for my company to make things easier to track. I have made a multiple items form to list the proposals. I want to be able to double click the record to open up another form (that i have already created) that shows the information from the proposal table and the proposal details table. So.....
I want to double click the field named "Proposal Name" and have it open up to form i have called "proposals details form".
I have a main form[frmResearchNotes] with combo box controls that filters a query populating [subfrmNotelist] containing several records from the filtered query. From there, I double click on a field within one of the remaining records, [CompanyName] for example, and it opens the new form[frmNoteDetail]. The problem is that second form is not displaying that selected record. The second form's record source has been set to the same query so when it loads, it displays the same info but it's displaying the 1st record out of the entire filtered list, not the record I clicked on in that list.
I figured I could use the strWhere function to copy the record I selected in the event procedure and then open the new form with those details. Not sure how to actually do this with VBA or if it's even the correct approach.
I would like to know if it's possible to open an specific subform inside a navigation form using an event.
I also need to to this using macros ( really can't use vba in this project =/)
Form example:
the main form has "nav_opt1", "nav_opt2", "nav_opt3"
By double clicking a record in "nav_opt1", it will open "nav_opt2" with some filters (but all in the same window), as if I was just browsing through the navigation forms usually.
