Sending string variable to table from form (vba)

I'm trying to create a form that is used to enter purchase orders into an Access 2003 database.
Traditionally I would set them up using the wizard, and use the basic objects from the toolbar corresponding to the fields in my table.

But I wanted to update the way a field is entered. I have a combo box that has 3 choices for a prefix (25,45,95), then the user types in a text box to enter a 8 digit number.When the user leaves the text field, the prefix and the number entered into the text box are combined using a global string variable.

The trouble I'm having is coding the 'add record' button to transfer the variable into my table.

The pseudo code would be:
[Add button clicked event.

(string variable), and All other fields in form to corresponding fields in table-(string variable) to specific field in table]

Sponsored Links:

Related Topics

Sending string variable to table from form (vba)
I'm trying to create a form that is used to enter purchase orders into an Access 2003 database.

Traditionally I would set them up using the wizard, and use the basic objects from the toolbar corresponding to the fields in my table.

But I wanted to update the way a field is entered. I have a combo box that has 3 choices for a prefix (25,45,95), then the user types in a text box to enter a 8 digit number.

When the user leaves the text field, the prefix and the number entered into the text box are combined using a global string variable.

The trouble I'm having is coding the 'add record' button to transfer the variable into my table.

[Add button clicked event.
(string variable), and All other fields in form to corresponding fields in table-(string variable) to specific field in table]


Pass String variable from Popup form to Main form in vba
I need an urgent response to this issue.

My Ms Access application has 2 forms viz:
a) The Main form
b) A popup form

The Main form calls the popup form via a button click. The popup form has a combo box whose value is stored in a global string variable.

I want to reference this string variable from the Main form (with the popup form still running i.e popup.visible = false


access vba string concatenation with variable within a loop
I wrote a vba subroutine to concatenate a string with the loop incremental variable to form a new variable. However, it seems like it translate it as a string rather than the variable. I tried to find the solution and found one similar issue one user had asked, but I can not find the answer.

The following is the post.

Const Value1 as long = 5
Const Value2 as long = 12
Const Value3 as long = 7

For I = 1 to 3
MsgBox Value & I '<-- How to do this properly?
Next I

I want MsgBox to display 5 then 12 then 7 rather than Value1 then Value2 then Value3
I can't figgure out how to get Value and I to combine properly to do this


What variable type to use with memo field in VBA
on VBA Access 2003/07,
I have a VBA class module that loads a single record from 1 of 4 tables (via a main form and datasheet subforms) to another form.
Most of the fields are keys however 1 field is memo data type.

What data type is best to use for the variable / property procedure in the class module. String seems to work but are there any limits in string length in vba, or a better way of achieving this. I seem to remember reading somewhere that memo datatype fields the data is not stored in the access db only a reference, is there any way of using this and passing the reference to the text box

Also the data is passed to the form via an ADO recordset am I right in saying that there is no issue with string length in ADO recordsets


Strings
The string is the most regularly used values of an application. We have seen in the past that a string variable could be declared using the String data type. After declaring a String variable, it is initialized as empty. You can also initialize it with any string of your choice.


Filting query results within VBA code module
I use Access 2010 but haven’t written much VBA code and could use some help. I have a table that provides the name of a query in a text field. The field name is “Query”.

I have a field in this same table that shows a department value for each record. That fieldname is “Dept”.

Within a huge VBA function module I’m writing, I have a string variable assigned to the Query field called strQuery. I also have string variable assigned to the Dept field called strDept.

The query that is assigned to the variable strQuery is designed to return ALL results for ALL departments. However, I want the VBA to run the query and return results for only the department that are assigned to the variable strDept.

If I leave out the department limitation, it works fine with the following code:


"create table" issue
First post on the forum! On to the question. I am trying to get a table created in VBA that gets its table name from a form entry thats stored in another table, passed into a variable, and used as my table name. im getting a syntax error message box when running this section and cant figure out what ive done wrong.

Relevant code follows but first some things ive already tried. saved the whole thing as a string variable and used docmd.runsql. ive used quotes around field name and datatype. im stuck on this one


Pass a list of parameters to a sql string variable
I have a form with a command button which runs a sql query in a string variable. The sql string basically counts the number of branches with less than 2000 customers from a table with Branch Id and customerTotal columns.

However I want to run this query maybe 10 times with different fixed parameters. I.e. wherecustomers is :


Embedded/nested? Variable
Is there some sort of internal variable or table that can be used to store temporary variables? While running code you can obviously store data in variable, the variable are dumped.

So I want to store some variable at some place in the vba or access that I can afterwards collect that variable again when I rerun my code.

Only way I know is to add it into a table, but is there some other, easier way?


SELECT in VBA Access (small db)
I want to invoke a small SQL statement to select datums from a table and pass that onto a string variable. Note I don't mean to dump the entire table into VBA but just 1x1 field, one at a time of course.

I have trouble finding how to use SELECT statements within VBA in a simple context as mine surley is. The fact is, the table contains the names of every checkbox in a form, I want VBA to get those names from the table, see if they = TRUE, and then APPEND to a new table (which, thankfully docmd.runSQL works for). It just seems much cleaner than having to pour in sixty control names into the VBA code.

I've heard about using querydefs but they seem way outside the scope of my application, which is small


refer to variable via string
Is it possible to refer to a variable from a string.

I can refer to a field by a string but can't get a string to be used aa a variable.

Heres what I have

rs.Fields(vField.name) = "a" & Replace(vField.name, " ","")

I know the first part works (before =) as I have tried just putting any value in the table and it works fine.

obviously the second part doesn't work but hope its clear what I'm after.

my variable name is 'aName' for example and my field would be 'Name'.

The reason I need this is so that I can look through all the field names and pull the correct data from the same named variable (with 'a' prefix


VBA doesn't recognzie variable's contents
I have declared a public variable in my form's vba code

[code]

Returns goes back to the original code.

But when I check the value of the variable in the originals form code with a msgbox. it is blank.

When the code finishes running (there is just one or two lines of code which do not affect the variable). I debug.print strinput and it has a value.

Why does debug.print recognize the value of the variable but the code within the form (it is an afterclick event subroutine) not recognize it? The public variable is declared in the original form.


Using Global Variables
Im developing a database and theres one part of it I seem to be seriously struggling on as im new to VBA.

My system as a login that is hard coded into a form and draws from a query in the database.

What I would like to do is declare a global variable that I can store the username in and use that variable in other forms.

Ive never used global variables before. Ive looked at other sights and know how to declare a global variable, but do not understand how I can set the list values of usernames to it.

Ive declared a glaobal variable in a module names UserSession like

'Global UserSession as String'

The form with a list of usernames has the syntax

List0.value = UserSession

but this fails to work


Substring Query
I'm interested in searching a table with a string valued field for variable definition statements. The sub string would be "AS ." I think this might be done effectively using a LIKE statement

SELECT [Raw Data].Field1, *
FROM [Raw Data]
WHERE ((([Raw Data].Field1) Like "* As *"));

However this will return the entire string containing the sub string, I'm only interested in the variable type, which comes three charterers after the A in AS. I can't use the Mid function, at least not in a trivial way, since the length before the AS varies.


USe CreateObject to create a new form
ya

I have a function that accepts a string variable that is the name of a form (e.g. "frmMain") and I need to create a new instance of the form, which I can't use the New word to do as it doesn't accept a string variable - so I was expecting to use CreateObject:

Sub subA (pstrFrmName as string)
Set gfrmAny = CreateObject("MyDB.form_" & pstrFrmName)
End Sub

But I receive error 'ActiveX component can't create object'


USe CreateObject to create a new form
ya

I have a function that accepts a string variable that is the name of a form (e.g. "frmMain") and I need to create a new instance of the form, which I can't use the New word to do as it doesn't accept a string variable - so I was expecting to use CreateObject:

Sub subA (pstrFrmName as string)
Set gfrmAny = CreateObject("MyDB.form_" & pstrFrmName)
End Sub

But I receive error 'ActiveX component can't create object'


VBA + SQL statement creation/execution.
I have a form with a lot of unbound fields. I chose this method because I need a lot of control over the form. I have completed everything I need, seemingly, except writing the information to the database.

I have declared a string variable to dynamically generate my INSERT sql statement then wish to execute it.

I am having two issues:

1) a string can only be 256 characters. what data type can I use for a much longer statement?

2) how do I execute my string (or whatever new datatype you suggest) against the database and have it return an error code so I can be sure it happened; for example, just using CurrentDB.Execute(sql) does not tell me if anything went wrong


RunSQL String Problem
I am trying to use the DoCmd.RunSQL command to add a new record to an existing table. The code is attached to a button and is as follows:

strSQL = "INSERT INTO RoomItemDetails (Roomref, QuoRef, ProductItemRef, Ordered) VALUES (intRoomid, [Quote Ref], '5', true)"

DoCmd.RunSQL strSQL

In VALUES, intRoomID is a declared integer variable holding an integer value; [Quote Ref] is a field known to this form; '5' is a string and true is a boolean Y/N state.

The programme doesn't seem to like the variable intRoomID, as it puts up a message box asking for it, but works as planned when this is given. I have tested that the variable gets the number; I populate the variable by using the output from a recordset of a table using rstTable(0): I have also tried using this in the Values section instead of intRoomID, but it thinks it is an undeclared function.


Using Variable as criteria
I have a procedure that constructs an In statement and stores that value in a module level variable. I then want to use criteria in my query to call the function to pull the value stored in the variable. I know that data being stored is constructed as required but when I run the query with the criteria to GetUIC() no records are returned. I have a control on my form that calls the GetUIC() function and displays the string that was built from my multi-select listbox. If I cut and paste that string as the criteria for my query the correct records are returned. Procedure below creates the string. The field is a text field.


Crop Variable String from Right
Sure this is easy if you know how - am trying to crop the last 6 digits from a variable sized text string using a query grid., have tried various things such as Right, Trim, etc., but not getting there.